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功能不允许这样做，如果是这种情况，那就没关系。但是，如果我能够完成这个操作，我将非常感激您能给我一个示例。

- Kenneth K.

---

你想解析具有不同根的XML吗？如果是这样，为什么要提到FOR XML？ - gbn

如果是这样，为什么要提到FOR XML呢？这是我的错。我混淆了在另一个论坛上得到的一个回复。由于根可能不同，那个解决方案不能满足我的需求。我只想在从数据库返回数据时忽略某些子元素。这是为了减少从数据库返回的不必要数据量的尝试。 - Kenneth K.

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:variable()来代替。我没有找到一种使节点数量动态化的方法，但是你可以添加尽可能多的节点，并将不需要的节点的值设置为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>

- Mikael Eriksson

---

我有一种感觉会变成这样。为了那些在我之后来的人，我本来想避免像连接查询这样复杂的操作——冒昧地说，那些在我之后来的人并不是最有技术的开发者（这不是我的话！）。我会再等一段时间看看是否有单个x.query()版本，但如果没有，我会标记你的答案为正确。谢谢。 - Kenneth K.