SQL Server XQuery - 选择子集

Question

以下面的 XML 为例：

初始数据

<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
</computer_book>

并且：

<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
    <pages>275</pages>
    <backing>paperback</backing>
</cooking_book>

我在 SQL Server 2008 数据库的单个 xml 类型列中有类似的内容。使用 SQL Server XQuery，是否可以获得如下结果：

结果数据

<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <pages>500</pages>
</computer_book>

以及：

<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
</cooking_book>

请注意，我并不是指在一个查询中选择两个示例；而是指在一个查询中选择两个示例。相反，我通过其主键（位于另一列中）选择每个。在每种情况下，我本质上是尝试选择根和子代的任意子集。正如上面所看到的，根可以不同，所以我不相信我可以将根节点名称硬编码到“for xml”子句中。

我有一种感觉 SQL Server 的 XQuery 功能不允许这样做，如果是这样也没关系。然而，如果我能做到这一点，我将非常感激一个例子。

Answer 1

这是我在下面的查询中使用的测试数据：

declare @T table (XMLCol xml)

insert into @T values 
('<computer_book>
    <title>Selecting XML Nodes the Fun and Easy Way</title>
    <isbn>9999999999999</isbn>
    <pages>500</pages>
    <backing>paperback</backing>
  </computer_book>'), 
('<cooking_book>
    <title>50 Quick and Easy XML Dishes</title>
    <isbn>5555555555555</isbn>
    <pages>275</pages>
    <backing>paperback</backing>
  </cooking_book>')

您可以使用 local-name() 和您想要的节点名称列表来过滤根节点下的节点：

select XMLCol.query('/*/*[local-name()=("isbn","pages")]')
from @T

结果：

<isbn>9999999999999</isbn><pages>500</pages>
<isbn>5555555555555</isbn><pages>275</pages>

如果我理解正确的话，这个问题是你没有拿回根节点。

此查询将为您提供一个空的根节点：

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'/>' as xml)
from @T

结果：

<computer_book />
<cooking_book />

由此我为您找到了两个解决方案。

解决方案 1

将表中的节点获取到表变量，然后将 XML 修改为您想要的样子。

-- Table variable to hold the node(s) you want
declare @T2 table (RootNode xml, ChildNodes xml)

-- Fetch the xml from your table
insert into @T2
select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'/>' as xml), 
       XMLCol.query('/*/*[local-name()=("isbn","pages")]')
from @T

-- Add the child nodes to the root node
update @T2 set
  RootNode.modify('insert sql:column("ChildNodes") into (/*)[1]')  

-- Fetch the modified XML
select RootNode
from @T2

结果：

RootNode
<computer_book><isbn>9999999999999</isbn><pages>500</pages></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages></cooking_book>

此解决方案的遗憾之处在于它不能与 SQL Server 2005 一起使用。

解决方案 2

获取各个部分，将 XML 构建为字符串并将其转换回 XML。

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>'+ 
            cast(XMLCol.query('/*/*[local-name()=("isbn","pages")]') as varchar(max))+
            '</'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>' as xml)
from @T

结果：

<computer_book><isbn>9999999999999</isbn><pages>500</pages></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages></cooking_book>

使节点参数化

在上面的查询中，作为子节点获得的节点在查询中被硬编码。您可以使用sql:varaible()来代替。我还没有找到使节点数量动态化的方法，但您可以添加您认为需要的任意数量，并将 null 作为您不需要的节点的值。

declare @N1 varchar(10)
declare @N2 varchar(10)
declare @N3 varchar(10)
declare @N4 varchar(10)

set @N1 = 'isbn'
set @N2 = 'pages'
set @N3 = 'backing'
set @N4 = null

select cast('<'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>'+ 
            cast(XMLCol.query('/*/*[local-name()=(sql:variable("@N1"),
                                                  sql:variable("@N2"),
                                                  sql:variable("@N3"),
                                                  sql:variable("@N4"))]') as varchar(max))+
            '</'+XMLCol.value('local-name(/*[1])', 'varchar(100)')+'>' as xml)
from @T

结果：

<computer_book><isbn>9999999999999</isbn><pages>500</pages><backing>paperback</backing></computer_book>
<cooking_book><isbn>5555555555555</isbn><pages>275</pages><backing>paperback</backing></cooking_book>