XML现场 - 查询(XML Field - Query)

我不能为了对有关XML领域我的SQL服务器2008年执行的任何查询，以建立一个正确的syntaxt。

我和领域，如Idproduct，产品名称和XMLPRODUCT一个简单的表。 ..和XMLPRODUCT场看起来像以下：

<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest">
  <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MyFirstValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
  </AllData>
</DynamicProfile>

举例来说，我需要编写一个查询其抽取包含节点值都行“MySecondKey”。 如何才能做到这一点？

Answer 1:

下面是使用XQuery运营商俺们 - >

declare @xmldata xml
    set @xmldata = 
    '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest">
  <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MyFirstValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  </AllData>
</DynamicProfile>'

;WITH XMLNAMESPACES 
(
    DEFAULT 'http://schemas.datacontract.org/2004/07/WinTest',
    'http://schemas.microsoft.com/2003/10/Serialization/Arrays' as d2p1
)
SELECT  x.c.value('(d2p1:Key)[1]', 'varchar(100)') as key3, x.c.value('(d2p1:Value)[1]', 'varchar(100)') as value
FROM @xmldata.nodes('/DynamicProfile/AllData/d2p1:KeyValueOfstringstring') x(c) where x.c.value('(d2p1:Key)[1]', 'varchar(100)') = 'MySecondKey'

Answer 2:

您可以使用下面的代码TSQL - >

declare @xmldata xml
    set @xmldata = 
    '<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest">
  <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MyFirstValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>
    <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MySecondKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  <d2p1:KeyValueOfstringstring>
      <d2p1:Key>MyFirstKey</d2p1:Key>
      <d2p1:Value>MySecondValue</d2p1:Value>
    </d2p1:KeyValueOfstringstring>  </AllData>
</DynamicProfile>'

 DECLARE @hDoc int, @rootxmlns varchar(100)
 SET @rootxmlns = '<AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays" />'

 EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata, @rootxmlns  

 SELECT *
 FROM OPENXML(@hDoc, '//d2p1:KeyValueOfstringstring',2)
 WITH ([d2p1:Key] varchar(100) , [d2p1:Value] varchar(100)) where [d2p1:Key] = 'MySecondKey'

 --clean up 
 EXEC sp_xml_removedocument @hDoc

文章来源: XML Field - Query