SQL Server Xml Namespace Querying Problem

Question

I have the following in an 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 can't seem to select the JournalNum values from it because of the xmlns=... on the node. In .Net I can do something like "{http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey}KeyData" to retrieve it, but I get a syntax error in SQL.

I just want to get a list of the Value nodes, in document order into a temp table and this doesn't work either....

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?

score 15 · Accepted Answer · answered Nov 02 '10 at 20:45

15

Got it...of course, right after asking

    ;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)

answered Nov 02 '10 at 20:45

CaffGeek

21,856
17
100
184

Thank you so much!! I have finally gotten rid of these pesky namespaces in my xpath queries. – Alberto De Caro Sep 23 '13 at 14:44

score 0 · Answer 2 · answered Jun 19 '17 at 12:56

Since you have only one namespace, you could have used DEFAULT to avoid having to 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 across on how to ignore all namespaces for when there are more than one and you KNOW you will have no collisions. Someone's blog.

SQL Server Xml Namespace Querying Problem

2 Answers2

Linked