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.
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
No comments:
Post a Comment