[SOLVED] How to delete a XML child node using SQL Server 2016

How to delete a XML child node using SQL Server 2016

I have the following Xml example snippet taken from a larger file:

<EntityAttributeValue>
  <Value />
  <Attribute>
    <Id>0</Id>
    <Name>Use 3</Name>
    <AttributeType>other</AttributeType>
  </Attribute>
  <AttributeValueId>999998</AttributeValueId>
  <ProductTypeId xsi:nil="true" />
</EntityAttributeValue>

I am trying to remove the <ProductTypeId> node using SQL so that the above will look like this:

<EntityAttributeValue>
  <Value />
  <Attribute>
    <Id>13</Id>
    <Name>Use 3</Name>
    <AttributeType>other</AttributeType>
  </Attribute>
  <AttributeValueId>999998</AttributeValueId>
</EntityAttributeValue>

I have used the following to SQL query the XML and isolate the above snippet(first one)

select t.c.query('.') as Attributes 
from @XMLData.nodes('/Item/ContentAttributeValues/EntityAttributeValue') t(c)
where t.c.value('(Attribute/Id)[1]','INT') = 0

I have tried

SET @XMLData.modify('delete (/Item/ContentAttributeValues/EntityAttributeValue/ProductTypeId)')

to remove all the product ids, but to no avail, any help would be greatly recieved.

Cheers

Solution

In the XML snippet you have provided, you have a different root. So, in order for your delete to work, all you need is to adjust the path:

SET @XMLData.modify('delete /EntityAttributeValue/ProductTypeId');