SQL Server Xml命名空间查询问题 [英] SQL Server Xml Namespace Querying Problem

问题描述

我在xml变量@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_>

但是由于节点上的xmlns=...，我似乎无法从中选择JournalNum值.在.Net中，我可以执行类似"{http://schemas.microsoft.com/dynamics/2006/02/documents/EntityKey}KeyData"的操作来检索它，但是在SQL中会出现语法错误.

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.

我只想按文档顺序将一个Value节点列表放入临时表中，但这也不起作用....

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?

推荐答案

知道了...当然，在问完之后

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)

这篇关于SQL Server Xml命名空间查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！