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

Please post a complete repro of the issue, as the `xml.modify(` appears to be correct. — David Browne - Microsoft, Feb 17 '20 at 15:17

score 2 · Accepted Answer · answered Feb 17 '20 at 15:17

2

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

answered Feb 17 '20 at 15:17

Roger Wolf

7,307
2
24
33

score 1 · Answer 2 · answered Feb 17 '20 at 15:59

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>

score 1 · Answer 3 · answered Feb 17 '20 at 16:03

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.

score 0 · Answer 4 · answered Feb 18 '20 at 08:34

0

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!!

answered Feb 18 '20 at 08:34

William Mather

23
4

Good to hear that the proposed solution is working for you. Please mark it as answered. You simply need to mark an answer as correct (the green check image). Click the green outlined checkmark to the left of the answer that solved your problem. This marks the answer as "accepted" – Yitzhak Khabinsky Feb 18 '20 at 20:13

How to delete a XML child node using SQL Server 2016

4 Answers4