T-SQL - XQuery - Delete Nodes From XML Document

When working with XML in SQL server you are using a sublanguage called XQuery inside of T-SQL. You have access to XML methods when using an XML typed value.  This query was written to be run by support, so it has some built in tests that assist support in knowing we are targeting the correct data. The keys here are:
Practice this in a test environment before you do it on prod. XML is tricky.

Understand the layout of your XML document. The one I was targeting was simple. We have some duplicate data and I wan to remove it.

<ResponseRoot attrib1='1' attrib2='2'>
<Answer id='77' value='work it out'>32</Answer>
<Answer id='78' value='work it out'>22</Answer>
<Answer id='79' value='work it out'>1112</Answer>
<Answer id='80' value='work it out'>112</Answer>

<Answer id='81' value='work it out'>12</Answer>
<Answer id='82' value='work it out'>42</Answer>
<Answer id='83' value='work it out'>552</Answer>
<Answer id='84' value='work it out'>92</Answer>
<Answer id='85' value='work it out'>2</Answer>

<Answer id='86' value='work it out'>12</Answer>
<Answer id='87' value='work it out'>42</Answer>
<Answer id='88' value='work it out'>552</Answer>
<Answer id='89' value='work it out'>92</Answer>
<Answer id='90' value='work it out'>201</Answer>


</ResponseRoot>

The [13] is part of XPath, which is used by XQuery to locate items in an XML document. Unlike C# (which enumerates nodes from 0), XQuery in T-SQL enumerates the nodes from 1.  Below I am looking for the 9th through 13th child nodes of the root to delete. I deleted them from bottom to top to prevent changing the absolute position of the nodes as I deleted them. Note that the 13th node is not determined by the data or the attributes of the node. Just by the ordinal position from the root.

I build the XML value in a variable by selecting the XML data from the table, updating it before I run the update, and then updating the row with the variable.
 

BEGIN TRAN

DECLARE

  @UserId INT = (SELECT UserId FROM User WITH (NOLOCK)  WHERE FIRSTNAME = 'Person' AND LASTNAME = 'Someone')

, @ObjectID varchar(99) = (SELECT ObjectID From Object WITH (NOLOCK) WHERE Name = 'Object name')

, @ResponseId INT

SET @ResponseId = (SELECT MAX(ObjectResponseID) From ObjectResponse WITH (NOLOCK) WHERE UserId = @UserId AND ObjectID = @ObjectID)

--Verify correct response is targeted

IF @UserId IS NOT NULL

BEGIN

IF @ResponseId = 123456

BEGIN

SELECT ObjectResponseXML Before FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

DECLARE @XDoc XML

SET @XDoc = (SELECT ObjectResponseXML FROM ObjectResponse WHERE ObjectResponseID = @ResponseId)

--Remove 13 before 12 to retain absloute order of preceding nodes you will delete

SET @XDoc.modify('delete (/ResponseRoot/Answer[13])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[12])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[11])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[10])')

SET @XDoc.modify('delete (/ResponseRoot/Answer[9])')

UPDATE ObjectResponse

SET ObjectResponseXML = @XDoc

WHERE ObjectResponseID = @ResponseId

SELECT ObjectResponseXML After FROM ObjectResponse WITH (NOLOCK)

WHERE ObjectResponseID = @ResponseId

END

ELSE Print 'Object response not found.'

END

ELSE Print 'User not found.'

ROLLBACK