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?

CaffGeek · Accepted Answer · 2010-11-02 20:45:45Z

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 2, 2010 at 20:45

CaffGeek

22.2k18 gold badges106 silver badges186 bronze badges

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

1 Comment

Alberto De Caro Over a year ago

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

Jay Wheeler · Accepted Answer · 2017-06-19 12:56:47Z

0

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.

answered Jun 19, 2017 at 12:56

Jay Wheeler

3792 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Server Xml Namespace Querying Problem

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related