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.

Answer 1

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>