SQL Server Xml query with multiple namespaces

Question

I have a table in SQL server that contains an Xml column and I am having trouble querying it. I don't know enough about XPath to determine if my query is wrong, or if it is because of what seems like conflicting namespaces. Here is an example xml:

<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope"
            xmlns:a="http://www.w3.org/2005/08/addressing" 
            xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
    <s:Header>
        <!-- snip -->
    </s:Header>
    <s:Body>
        <FetchRequest xmlns="http://www.foobar.org/my/schema">
            <Contract xmlns:a="http://www.foobar.org/2014/04/datacontracts"
                      xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                <a:RequestedBy>John Doe</a:RequestedBy>
                <a:TransactionId>ABC20140402000201</a:TransactionId>
            </Contract>
        </FetchRequest>
    </s:Body>
</s:Envelope>

I want to retrieve TransactionId from the xml. The query I tried was this:

SELECT TOP 100
MessageXml,
MessageXml.value('
    declare namespace s="http://www.w3.org/2003/05/soap-envelope";
    declare namespace a="http://www.w3.org/2005/08/addressing";
    (/s:Envelope/s:Body/FetchRequest/Contract/a:TransactionId)[1]', 'varchar(max)')
FROM dbo.Message

I am getting back NULL for my MessageXml.value. If I remove everything after s:Body I seem to get a bunch of text that is concatenated, but as soon as I add FetchRequest I get NULL back in my results.

I did notice that the Contract element defines a namespace of a, and the Envelope also defines a namespace of a, but I wasn't sure if that is a problem or not.

How can I retrieve TransactionId using an XPath query given the above xml example?

vittore · Accepted Answer · 2014-12-12 18:12:10Z

43

I know that answer is accepted, but there is actually simplier way of doing it, if the only thing you need to do is select node value. Just use * as namespace name:

SELECT MessageXml
     ,  MessageXml.value('(/*:Envelope/*:Body/*:FetchRequest/*:Contract/*:TransactionId)[1]'
                       , 'varchar(max)')
FROM   dbo.Message

answered Dec 12, 2014 at 18:12

vittore

17.6k6 gold badges46 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MikeTeeVee Over a year ago

This is the only acceptable answer to me. I work in a 3rd-Party database where they use various namespaces in the same column, but they mostly follow the same schema (at least the schema-path I care about extracting). This approach also means simpler/less scripting and avoids breaking over time when namespaces change (or are versioned to add more schema). Thank you!

vittore Over a year ago

@MikeTeeVee that was exactly my case when i found it. Glad it helped!

marc_s · Accepted Answer · 2014-04-02 17:27:18Z

23

You have two problems :

you're not respecting the implicit default XML namespace on the <FetchRequest> node
the XML namespace with the a: prefix is first defined on the <s:Envelope> node, and is being re-declared on the <Contract> node (really really bad practice in my opinion) and you need to use the second declaration for anything below the <Contract> node.

So you need something like this (I prefer to define the XML namespaces upfront, in a WITH XMLNAMESPACES() statement):

;WITH XMLNAMESPACES('http://www.w3.org/2003/05/soap-envelope' AS s,
                    'http://www.foobar.org/2014/04/datacontracts' AS a,
                    'http://www.foobar.org/my/schema' AS fb)
SELECT 
    MessageXml,
    MessageXml.value('(/s:Envelope/s:Body/fb:FetchRequest/fb:Contract/a:TransactionId)[1]', 'varchar(max)')
FROM 
    dbo.Message

This will output the whole query and the value ABC20140402000201 for your second column.

answered Apr 2, 2014 at 17:27

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

3 Comments

Dismissile Over a year ago

I will just comment that I didn't create the XML schema, I am just trying to query it.

Dismissile Over a year ago

After fixing my predicate and following this, I was able to get the results. Thanks for the help!

Buminda Over a year ago

Thanks for sharing, I was struggling with multiple name spaes.. this saved my day.

StuartLC · Accepted Answer · 2014-04-02 18:35:12Z

5

FetchRequest and Contract are in namespace http://www.foobar.org/my/schema and alias a is redefined in the document. You need:

SELECT TOP 100
MessageXml,
MessageXml.value('declare namespace s="http://www.w3.org/2003/05/soap-envelope";
    declare namespace a="http://www.w3.org/2005/08/addressing";
    declare namespace f="http://www.foobar.org/my/schema";
    declare namespace x="http://www.foobar.org/2014/04/datacontracts";
    (/s:Envelope/s:Body/f:FetchRequest/f:Contract/x:TransactionId)[1]', 'varchar(max)')
FROM dbo.Message;

Fiddle here:

edited Apr 2, 2014 at 18:35

answered Apr 2, 2014 at 17:28

StuartLC

108k18 gold badges225 silver badges294 bronze badges

Collectives™ on Stack Overflow

SQL Server Xml query with multiple namespaces

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related