select query to remove nodes from xml column

Question

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

Currently, my query basically looks like this:

select top 1000 [MyXmlData] from [MyTable]

The MyXmlData column contains xml that 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>

I am using SQL Server 2008.

Mikael Eriksson · Accepted Answer · 2011-05-31 13:08:35Z

3

This is tested on SQL Server

You can store your query result to a temp table or table variable and use modify() to delete the Image nodes. Use contains() and local-name() to figure out if a node should be deleted or not.

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

Result:

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

answered May 31, 2011 at 13:08

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

select query to remove nodes from xml column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related