Geek asks and answers

Select query to remove nodes from xml column

I have a table with an XML column that contains 2 nodes that have large base64 rows (images). When I query the database, I want to remove these 2 nodes from the xml returned to the client. I cannot change the table schema (i.e. I cannot split the data in a column). How to remove 2 nodes from xml column using select statement? (The nodes for deleting both contain the text "Image" in their name). Up to 1000 records can be returned in any one request.

Currently my query looks something like this:

select top 1000 [MyXmlData] from [MyTable]

The MyXmlData column contains xml, which looks something like this:

<MyXml>
   <LotsOfNodes></LotsOfNodes>
   ...
   <ANode>
      ...
      <MyImage1></MyImage1>   <!-- remove this from returned xml -->
      <MyImage2></MyImage2>   <!-- remove this from returned xml -->
      ...
   </ANode>
   ...
   <LotsOfNodes></LotsOfNodes>
   ...
</MyXml>
+3
source share
1 answer

This is tested on SQL Server.

temp modify() Image. () local-name(), node .

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>
+3

More articles:


All Articles