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?

Solution

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)

Comments

[Alberto De Caro]

Thank you so much!! I have finally gotten rid of these pesky namespaces in my xpath queries.