Delete an element from a certain row in an XML-type column in SQL?

Question

Suppose I have the following:

CREATE TABLE tblData ( [SourceID] int, [SourceRecID] bigint, [Value] xml )
GO

INSERT INTO tblData
VALUES
( 1, 0, N'<attributes><attribute id="58" value="0" /><attribute id="86" value="1" /><attribute id="85" value="1" /><attribute id="70" value="0" /><attribute id="38" value="0" /><attribute id="68" value="0" /><attribute id="42" value="1" /><attribute id="67" value="1" /><attribute id="62" value="1" /></attributes>' ), 
( 1, 686, N'<attributes><attribute id="1" value="0.25" /><attribute id="4" value="1" /><attribute id="10" value="3" /><attribute id="11" value="1" /><attribute id="12" value="6" /></attributes>' ), 
( 1, 687, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ), 
( 1, 688, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' )
GO

SELECT *
FROM tblData

Let's say I would like to delete attribute id=7 from the Value column where SourceId = 1, SourceRecID = 687.

I would expect the new value of the row SourceId = 1, SourceRecID = 687 to be:

N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ),

Notice that <attribute id="7" value="1" /> is now missing from the original xml for that row.

I can't think of a way to do it...

Considering that DELETE FROM tblData WHERE SourceID = 1; would delete every row, why do the latter 2 requirements matter? — Thom A
– Thom A ♦, Commented Dec 12, 2018 at 15:45
Are you trying to remove the entire row when that attribute ID = 7 or just remove that section from the [Value] field? — precose
– precose, Commented Dec 12, 2018 at 15:48
I don't want to delete the row. I just want to remove attribute id="7" from the Value field in that row. I hope that makes it clearer. — Denis
– Denis, Commented Dec 12, 2018 at 15:48
@Denis what you ask is possible and described in Examples of using XQuery to update XML Data in SQL Server. You can use modify with the delete keyword to delete nodes that match a query, eg UPDATE HR_XML SET Salaries.modify('delete (/Salaries/Accounting)[2]'). I didn't know that until now by the way — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 12, 2018 at 15:52

Panagiotis Kanavos · Accepted Answer · 2018-12-12 16:04:24Z

You can try :

update tblData 
set 
    Value.modify('delete (/attributes/attribute[@id="7"])')
where 
    SourceRecID = 687 and 
    SourceID = 1

SQL Server's XQuery contains an extension, XML DML that allows modifying XML values. Several examples are shown in Examples of using XQuery to update XML Data in SQL Server.

The modify function is used to execute an XMLDML query. In that query, the delete keyword can be used to delete all elements that match a query

Using the following script :

declare @tblData table ( [SourceID] int, [SourceRecID] bigint, [Value] xml );


INSERT INTO @tblData
VALUES
( 1, 0, N'<attributes><attribute id="58" value="0" /><attribute id="86" value="1" /><attribute id="85" value="1" /><attribute id="70" value="0" /><attribute id="38" value="0" /><attribute id="68" value="0" /><attribute id="42" value="1" /><attribute id="67" value="1" /><attribute id="62" value="1" /></attributes>' ), 
( 1, 686, N'<attributes><attribute id="1" value="0.25" /><attribute id="4" value="1" /><attribute id="10" value="3" /><attribute id="11" value="1" /><attribute id="12" value="6" /></attributes>' ), 
( 1, 687, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="9" value="1" /><attribute id="10" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' ), 
( 1, 688, N'<attributes><attribute id="1" value="2.00" /><attribute id="2" value="60.00" /><attribute id="3" value="-1" /><attribute id="5" value="252.00" /><attribute id="6" value="0" /><attribute id="7" value="1" /><attribute id="11" value="2" /><attribute id="12" value="10" /></attributes>' )

The contents of the row with SourceRecID 687 are :

<attributes>
  ...
  <attribute id="6" value="0" />
  <attribute id="7" value="1" />
  <attribute id="9" value="1" />
  ...
</attributes>

After executing this query :

update tblData 
set Value.modify('delete (/attributes/attribute[@id="7"])')
where [SourceRecID]=687 and SourceID = 1

The contents are :

...
<attribute id="5" value="252.00" />
<attribute id="6" value="0" />
<attribute id="9" value="1" />
...

I deleted my answer because I see you made your comment an answer. I'll mark this as the correct answer.

Collectives™ on Stack Overflow

Delete an element from a certain row in an XML-type column in SQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related