logo标签列表

如果XML节点的子元素包含给定值，则删除TSQL中的XML节点

xmlt-sql
5

XML存储可以在<Addresses>下有零个或多个<Address>节点。

需要查询来删除任何<Address>节点，其中<AddressLine> = '2 Church Lane'

起始XML：

declare @t table (storage xml);
insert into @t (storage)
select '<Person>
  <Fullname />
  <Surname>Smith</Surname>
  <FirstName>John</FirstName>
  <Addresses>
      <Address>
        <AddressLine>1 Church Lane</AddressLine>
      </Address>
      <Address>
        <AddressLine>2 Church Lane</AddressLine>
      </Address>
      <Address>
        <AddressLine>3 Church Lane</AddressLine>
      </Address>
  </Addresses>
</Person>';

生成的XML：

<Person>
  <Fullname />
  <Surname>Smith</Surname>
  <FirstName>John</FirstName>
  <Addresses>
      <Address>
        <AddressLine>1 Church Lane</AddressLine>
      </Address>
      <Address>
        <AddressLine>3 Church Lane</AddressLine>
      </Address>
  </Addresses>
</Person>
1个回答
5

找到了：

update @t
SET storage.modify('
    delete (/Person/Addresses/Address[AddressLine = "2 Church Lane"])
')
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接