SQL Server Xml namespace query issue

Question

I have the following in xml variable @ResultData

 <EntityKey_x005B__x005D_> <EntityKey> <KeyData xmlns="http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey"> <KeyField> <Field>JournalNum</Field> <Value>LJRN000071</Value> </KeyField> </KeyData> </EntityKey> <EntityKey> <KeyData xmlns="http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey"> <KeyField> <Field>JournalNum</Field> <Value>LJRN000072</Value> </KeyField> </KeyData> </EntityKey> <EntityKey> <KeyData xmlns="http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey"> <KeyField> <Field>JournalNum</Field> <Value>LJRN000073</Value> </KeyField> </KeyData> </EntityKey> <EntityKey> <KeyData xmlns="http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey"> <KeyField> <Field>JournalNum</Field> <Value>LJRN000074</Value> </KeyField> </KeyData> </EntityKey> </EntityKey_x005B__x005D_> 

But I cannot select JournalNum values ​​from it due to xmlns=... on node. In .Net, I can do something like "{http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey}KeyData" to get it, but I get a syntax error in SQL.

I just want to get a list of Value nodes in the order of the document in the temp table, and this will not work.

 SELECT IDENTITY(int,1,1) as 'ID', c.query('(KeyData/KeyField/Value)[1]') as 'JournalNum' INTO #tmpBatches FROM @ResultData.nodes('//EntityKey') t(c) 

Thoughts? Suggestions? Solutions?

Answer 1

Got it ... of course, right after the request

  ;WITH XMLNAMESPACES (N'http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey' as DYN) SELECT IDENTITY(int,1,1) as 'ID', c.value('(DYN:KeyData/DYN:KeyField/DYN:Value)[1]', 'VARCHAR(40)') as 'JournalNum' INTO #tmpBatches FROM @ResultData.nodes('//EntityKey') t(c) 

Answer 2

Since you only have one namespace, you could use DEFAULT to avoid the prefix everywhere:

  ;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey') SELECT IDENTITY(int,1,1) as 'ID', c.value('(<strike>DYN:</strike>KeyData/DYN:KeyField/DYN:Value)[1]', 'VARCHAR(40)') as 'JournalNum' INTO #tmpBatches FROM @ResultData.nodes('//EntityKey') t(c) 

Also, some notes I stumbled upon how to ignore all namespaces when there are more than one, and you KNOW that you will not have collisions. Someone blog.