Select XML node with namespaces

Question

Select XML node with namespaces

carlos FSU

SSC-Addicted

Points: 440
More actions
February 15, 2012 at 11:42 am

#250759

I am trying to select data from a column that has xml data in it. I have tried all I can think of, but the results keep coming back as NULL. Not sure why but I pasted the top of the xml document so show what I am trying to do. Any help is GREATLY appreciated.
- <ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">
- <ST>
<ST01_TransactionSetIdentifierCode>837</ST01_TransactionSetIdentifierCode>
<ST02_TransactionSetControlNumber>1001</ST02_TransactionSetControlNumber>
<ST03_ImplementationGuideVersionName>005010X222A1</ST03_ImplementationGuideVersionName>
</ST>
And the query...
WITH XMLNAMESPACES('[ns0]' AS ns)
SELECT sourceclaimxml.value('(/ST01_TransactionSetIdentifierCode)[2]', 'varchar(300)') XMLValue
FROM xclaim_audit_xml
CROSS APPLY sourceclaimxml.nodes('//ST') as P(nref)
WHERE edixid='2323111'

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

The basic problem is that your document has a somewhat weird structure: only the root element is in namespace 'http://schemas.microsoft.com/BizTalk/EDI/X12/2006'. All contained

elements are in an unnamed namespace. By default this is the namespace '' in SQL server.

I suspect that you intended to have the entire document in this namespace. If so, let me know and I think I can give you some hints how to properly specify the document. But to answer the question that you did post, here's an explanation how to get your data from the document you posted.

To retrieve data from your document you need to read the root node from the namespace. All other elements are to be retrieved from the default namespace (i.e. don't do anything special).

declare @xml xml = N'

<ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">

<ST>

<ST01_TransactionSetIdentifierCode>837</ST01_TransactionSetIdentifierCode>

<ST02_TransactionSetControlNumber>1001</ST02_TransactionSetControlNumber>

<ST03_ImplementationGuideVersionName>005010X222A1</ST03_ImplementationGuideVersionName>

</ST>

</ns0:X12_00501_837_P>';

-- Use the xmlnamespaces option. This works, but it has the disadvantage that it also

-- puts the namespace in any xml constructed in the same statement.

with xmlnamespaces( 'http://schemas.microsoft.com/BizTalk/EDI/X12/2006' as xx)

select r.r.query('.')

FROM @xml.nodes('/xx:X12_00501_837_P') r(r)

-- Alternatively use a local namespace declaration in the nodes query only. This way the namespace

-- is only used while evaluating the .nodes() and is not put into any xml output by the query.

select r.r.query('.')

FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)

-- Note that I used xx as the namespace abbreviation. I intentionally did

-- not use "ns0" to illustrate that this is only a name local to your

-- document. The prefix you use in the query does not need to match

-- that in the document, only the name of the namespace

-- ("http://schemas.microsoft.com/BizTalk/EDI/X12/2006" in your case)

-- needs to match.

-- A simple trick I often use to see what is going on is to .query('.') the entire node returned:

select r.r.query('.')

FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)

-- Now add in the cross apply to select all ST elements (without a namespace on them):

select r.r.query('.'),

s.s.query('.')

FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)

cross apply r.r.nodes('ST') s(s)

-- A working example to read the contents of your document:

select s.s.value('ST01_TransactionSetIdentifierCode[1]', 'varchar(100)'),

s.s.value('ST02_TransactionSetControlNumber[1]', 'varchar(100)'),

s.s.value('ST03_ImplementationGuideVersionName[1]', 'varchar(100)')

FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)

cross apply r.r.nodes('ST') s(s)

Hope to help,

rrozema

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

carlos FSU SSC-Addicted Points: 440 More actions · Answer 2

That is absolutley awesome. It worked perfectly. Yes, I did not include the bottom piece of the xml as I was just trying to add a sample piece.

THANK YOU so much for your help. :-D:-D:-D

carlos FSU SSC-Addicted Points: 440 More actions · Answer 3

I did have one follow up question. If I wanted to go a bit deeper into the XML, do I just simply add another cross apply? For example, if I wanted to read the node below "BHT04_TransactionSetCreationDate" how do I account for the namespace? Thank you again for your help.

<ns0:X12_00501_837_P xmlns:ns0="http://schemas.microsoft.com/BizTalk/EDI/X12/2006">

<ST>

<ST01_TransactionSetIdentifierCode>837</ST01_TransactionSetIdentifierCode>

<ST02_TransactionSetControlNumber>1001</ST02_TransactionSetControlNumber>

<ST03_ImplementationGuideVersionName>005010X222A1</ST03_ImplementationGuideVersionName>

</ST>

<ns0:BHT_BeginningofHierarchicalTransaction>

<BHT01_HierarchicalStructureCode>0019</BHT01_HierarchicalStructureCode>

<BHT02_TransactionSetPurposeCode>00</BHT02_TransactionSetPurposeCode>

<BHT03_OriginatorApplicationTransactionIdentifier>1234569</BHT03_OriginatorApplicationTransactionIdentifier>

<BHT04_TransactionSetCreationDate>20120207</BHT04_TransactionSetCreationDate>

<BHT05_TransactionSetCreationTime>1050</BHT05_TransactionSetCreationTime>

<BHT06_ClaimorEncounterIdentifier>LP</BHT06_ClaimorEncounterIdentifier>

</ns0:BHT_BeginningofHierarchicalTransaction>

<SE>

<SE01_TransactionSegmentCount>8590</SE01_TransactionSegmentCount>

<SE02_TransactionSetControlNumber>1001</SE02_TransactionSetControlNumber>

</SE>

</ns0:X12_00501_837_P>

R.P.Rozema SSChampion Points: 12317 More actions · Answer 4

It depends on the number repetitions of each element you expect in your document. Looking at this example document it looks like there is always going to be exactly 1 <ST> element, followed by exactly 1 <ns0:BHT_BeginningofHierarchicalTransaction> element, followed by exactly one <SE> element. If so, you don't need the .nodes() function. You can simply retrieve the values like so:

select r.r.value('ST[1]/ST01_TransactionSetIdentifierCode[1]','varchar(300)'),

r.r.value('ST[1]/ST02_TransactionSetControlNumber[1]','int'),

r.r.value('ST[1]/ST03_ImplementationGuideVersionName[1]','varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT01_HierarchicalStructureCode[1]', 'varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT02_TransactionSetPurposeCode[1]', 'varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT03_OriginatorApplicationTransactionIdentifier[1]', 'varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT04_TransactionSetCreationDate[1]', 'varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT05_TransactionSetCreationTime[1]', 'varchar(300)'),

r.r.value('declare namespace x="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; x:BHT_BeginningofHierarchicalTransaction[1]/BHT06_ClaimorEncounterIdentifier[1]', 'varchar(300)'),

r.r.value('SE[1]/SE01_TransactionSegmentCount[1]','int'),

r.r.value('SE[1]/SE02_TransactionSetControlNumber[1]','int')

FROM @xml.nodes('declare namespace xx="http://schemas.microsoft.com/BizTalk/EDI/X12/2006"; /xx:X12_00501_837_P') r(r)

If for example your document always has 2 of these elements you can access the members of each of those two elements like this: