How can I select tags from an SQL XML Query?

Question

How can I retrieve the fields within an XML field in MS SQL?

Every query I try does not work as intended whenever I use this XML code:

<soap:Envelope xmlns:xsi="[URI]" xmlns:xsd="[URI]" xmlns:soap="[URI]">
  <soap:Body>
    <RunPackage xmlns="[URI]">
      <xmlDoc>
        <Request>
          <SubscriberCode>76547654</SubscriberCode>
          <CompanyCode></CompanyCode>
        </Request>
      </xmlDoc>
    </RunPackage>
  </soap:Body>
</soap:Envelope>

I don't know how to reference the first two tags. I've tried

SELECT TransactionID, T2.Loc.query('data(Request/SubscriberCode)') as 'SubscriberCode'
FROM   TempWorksRequest
CROSS APPLY RequestXML.nodes('soap:Envelope/soap:Body/RunPackage/xmlDoc') as T2(Loc)

With no luck.

marc_s · Accepted Answer · 2009-06-11 20:37:33Z

3

You need to declare the XML namespaces ("soap" in this case, plus another one for the node and anything below) in your XQuery operations:

SELECT 
   TransactionID, 
   T2.Loc.query('declare namespace ns="[URI1]";data(ns:Request/ns:SubscriberCode)') 
     as 'SubscriberCode'
FROM   
   TempWorksRequest
CROSS APPLY 
   RequestXML.nodes('declare namespace soap="[URI]";
                     declare namespace ns="[URI1]";
                     soap:Envelope/soap:Body/ns:RunPackage/ns:xmlDoc') as T2(Loc)

[URI1] needs to be the URI that's defined on the <RunPackage> tag.

Marc

edited Jun 11, 2009 at 20:37

answered Jun 11, 2009 at 20:29

marc_s

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

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

2 Comments

KTF Over a year ago

Thank you so much! This almost functioned verbatim. I noticed I also was missing a XML declaration tag and was not using the correct namespace URIs!

cciotti Over a year ago

Great answer, it helped me as well.

Collectives™ on Stack Overflow

How can I select tags from an SQL XML Query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related