sql - 选择查询以从 xml 列中删除节点

Question

我有一个包含 XML 列的表，其中包含 2 个具有大型 base64 字符串(图像)的节点。当我查询数据库时，我想从返回给客户端的xml中删除这2个节点。我无法更改表的模式(即我无法拆分列中的数据)。如何使用 select 语句从 xml 列中删除 2 个节点？ (要删除的节点都在其名称中包含文本“图像”)。任何单个查询最多可返回 1000 条记录。

目前，我的查询基本上是这样的:

select top 1000 [MyXmlData] from [MyTable]

MyXmlData 列包含如下所示的 xml:

<MyXml>
   <LotsOfNodes></LotsOfNodes>
   ...
   <ANode>
      ...
      <MyImage1></MyImage1>   <!-- remove this from returned xml -->
      <MyImage2></MyImage2>   <!-- remove this from returned xml -->
      ...
   </ANode>
   ...
   <LotsOfNodes></LotsOfNodes>
   ...
</MyXml>

我正在使用 SQL Server 2008。

Answer 1

这是在 SQL Server 上测试的

您可以将查询结果存储到临时表或表变量中并使用 modify()至 delete Image 节点。使用 contains()和 local-name()以确定是否应删除节点。

declare @T table(XmlData xml)

insert into @T values
('<MyXml>
    <LotsOfNodes></LotsOfNodes>
    <ANode>
      <MyImage1></MyImage1>   <!-- remove this from returned xml -->
      <MyImage2></MyImage2>   <!-- remove this from returned xml -->
    </ANode>
    <LotsOfNodes></LotsOfNodes>
  </MyXml>')

update @T set
  XmlData.modify('delete //*[contains(local-name(.), "Image")]')

select *
from @T

结果:

<MyXml>
  <LotsOfNodes />
  <ANode>
    <!-- remove this from returned xml -->
    <!-- remove this from returned xml -->
  </ANode>
  <LotsOfNodes />
</MyXml>