SQL XML如何查询特定的节点

I'm playing around with SQL and XML. I have a column in our database (properties) where XML is stored.

我在使用SQL和XML。我在数据库(属性)中有一列存储XML。

An example of this XML looks like this:

这个XML的一个例子是这样的:

<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.xyz.de/activerepository/fileprops">
  <props>
    <prop ns="ARM:" elem="_NoFilter">
      <value xsi:type="xsd:boolean">true</value>
    </prop>
    <prop ns="DAV:" elem="displayname">
      <value xsi:type="xsd:string">Some text in it</value>
    </prop>
    <prop ns="DAV:" elem="getcontenttype">
      <value xsi:type="xsd:string">message/rfc822</value>
    </prop>
    <prop ns="DAV:" elem="creationdate">
      <value xsi:type="xsd:dateTime">2017-01-02T09:38:28.1278078</value>
    </prop>
  </props>
</root>

I've written a query, where I get all props of this XML structure.

我编写了一个查询，在这里我得到了这个XML结构的所有支持。

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
SELECT 
    cast(properties as xml).query('root/props/prop'),
    *
FROM   tm_cas_files (nolock)
where  id = 'A7ED7D99-8CDB-47F6-8EF5-18E7FCB97F28'

This query works fine, but I get all nodes of the XML structure.

这个查询运行良好，但是我获得了XML结构的所有节点。

The result of this query looks like this:

这个查询的结果如下:

<p1:prop xmlns:p1="http://www.xyz.de/activerepository/fileprops" ns="ARM:" elem="_NoFilter">
  <p1:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:boolean">true</p1:value>
</p1:prop>
<p2:prop xmlns:p2="http://www.xyz.de/activerepository/fileprops" ns="DAV:" elem="displayname">
  <p2:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">Some text in it</p2:value>
</p2:prop>
<p3:prop xmlns:p3="http://www.xyz.de/activerepository/fileprops" ns="DAV:" elem="getcontenttype">
  <p3:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:string">message/rfc822</p3:value>
</p3:prop>
<p4:prop xmlns:p4="http://www.xyz.de/activerepository/fileprops" ns="DAV:" elem="creationdate">
  <p4:value xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="xsd:dateTime">2017-01-02T09:38:28.1278078</p4:value>
</p4:prop>

How can I get now only a specific node of this XML?

现在如何才能只获得这个XML的特定节点?

I've already tested this query (to get i.e. "_NoFilter" node), but this SQL query returns no / empty result:

我已经测试了这个查询(获得“_NoFilter”节点)，但是这个SQL查询返回no / empty结果:

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
SELECT 
    cast(properties as xml).query('root/props/prop[elem="_NoFilter"]'),
    *
FROM   tm_cas_files (nolock)
where  id = 'A7ED7D99-8CDB-47F6-8EF5-18E7FCB97F28'

Thanks for your help!

谢谢你的帮助!

2 个解决方案

#1

If you just want the _NoFilter node, you can use XPath to select only this one.

如果只想要_NoFilter节点，可以使用XPath只选择这个节点。

/root/props/prop[@elem="_NoFilter"]

or maybe even

甚至

/root/props/prop[@elem="_NoFilter"]/value

shorthand:

速记:

//prop[@elem="_NoFilter"]/value

#2

If you already know the index of the item, one way is

如果您已经知道项目的索引，一种方法是

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
SELECT 
    @xml.query('root/props/prop[2]')

Or better you can use following SQL XML Query

或者您可以使用以下SQL XML查询

;WITH XMLNAMESPACES(DEFAULT 'http://www.xyz.de/activerepository/fileprops')
select
    val.value('.','nvarchar(100)') as [PropValue]
from @xml.nodes('/root/props/prop[@elem="_NoFilter"]/value') as p(val)

#1