How to delete a XML child node using SQL Server 2016

Question

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

Answer 1

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');

Answer 2

Here is a full example. I had to add a namespace to the root to accommodate the xsi:nil="true" attribute.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata XML NOT NULL);
INSERT INTO @tbl (xmldata) VALUES
(N'<Item xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <ContentAttributeValues>
        <EntityAttributeValue>
            <Value/>
            <Attribute>
                <Id>0</Id>
                <Name>Use 3</Name>
                <AttributeType>other</AttributeType>
            </Attribute>
            <AttributeValueId>999998</AttributeValueId>
            <ProductTypeId xsi:nil="true"/>
        </EntityAttributeValue>
        <EntityAttributeValue>
            <Value/>
            <Attribute>
                <Id>10</Id>
                <Name>Use 7</Name>
                <AttributeType>other</AttributeType>
            </Attribute>
            <AttributeValueId>999770</AttributeValueId>
            <ProductTypeId xsi:nil="true"/>
        </EntityAttributeValue>
    </ContentAttributeValues>
</Item>');
-- DDL and sample data population, end

UPDATE @tbl
SET xmldata.modify('delete /Item/ContentAttributeValues/EntityAttributeValue/ProductTypeId');

SELECT * FROM @tbl;

Output XML

<Item xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ContentAttributeValues>
    <EntityAttributeValue>
      <Value />
      <Attribute>
        <Id>0</Id>
        <Name>Use 3</Name>
        <AttributeType>other</AttributeType>
      </Attribute>
      <AttributeValueId>999998</AttributeValueId>
    </EntityAttributeValue>
    <EntityAttributeValue>
      <Value />
      <Attribute>
        <Id>10</Id>
        <Name>Use 7</Name>
        <AttributeType>other</AttributeType>
      </Attribute>
      <AttributeValueId>999770</AttributeValueId>
    </EntityAttributeValue>
  </ContentAttributeValues>
</Item>

Answer 3

From your code I take, that you are searching for the <ProductTypeId> of the EAV-element, where the <Attribute><Id> has the value 0.

Your question is not all clear, but I think you might be looking for this:

declare @tbl TABLE(ID INT IDENTITY, YourXml XML);
INSERT INTO @tbl VALUES
(N'<Item xmlns:xsi="blahblah">
  <ContentAttributeValues>
    <EntityAttributeValue>
      <Value />
      <Attribute>
        <Id>0</Id>                                     <!-- Id value = 0  -->
        <Name>Use 3</Name>
        <AttributeType>other</AttributeType>
      </Attribute>
      <AttributeValueId>999998</AttributeValueId>
      <ProductTypeId xsi:nil="true" />                 
    </EntityAttributeValue>
    <EntityAttributeValue>
      <Value />
      <Attribute>
        <Id>1</Id>                                      <!-- Other Id value!!!  -->
        <Name>Use 3</Name>
        <AttributeType>other</AttributeType>
      </Attribute>
      <AttributeValueId>999998</AttributeValueId>
      <ProductTypeId xsi:nil="true" />
    </EntityAttributeValue>
  </ContentAttributeValues>
</Item>');

UPDATE @tbl SET YourXml.modify(N'delete /Item
                                        /ContentAttributeValues
                                        /EntityAttributeValue[Attribute/Id = 0]
                                        /ProductTypeId');

SELECT * FROM @tbl;

The XPath stands for: Dive into the EAV-element and filter for the element(s), which answer the predicate. Below this EAV-element find the <ProductTypeID> and delete it.

In the result you will find, that the node was deleted only for the first EAV-element, which has Id=0.

Answer 4

Hi All and thank you for your comments.

apologies for not making my code clear, i thought it was in my head and it was late in the day! lol.

The SET @XMLData.modify('delete(/Item/ContentAttributeValues/EntityAttributeValue/ProductTypeId)') worked a treat.. I had tried that before but i was getting errors, I know realise that the parentheses after the delete, is kinda key to it working ha! thank you all again!!