Editing XML From SQL Query Using XML-DML

Question

I have an XML column in my MSSQL database whose schema looks similar to this:

<Form>
   <Version>1000</Version>
   <OtherValues />
</Form>

And I need to manually change (via script) all rows' Version numbers to 1001. Upon searching, I can infer that I'm going to be using the .modify XPath function but all examples I've found have been for inserting nodes, not editing them.

Could someone shed some light on how to do this?

Answer 1

Example data setup:

DECLARE @t TABLE (
    Id int
    , X xml
)

INSERT @t VALUES ( 1, '
<Form>
   <Version>1000</Version>
   <OtherValues />
</Form>
'
)

INSERT @t VALUES ( 2, '
<Form>
   <Version>1000</Version>
   <OtherValues />
</Form>
'
)

Pre-change data:

SELECT * FROM @t

Id          X
----------- ------------------------------------------------------------
1           <Form><Version>1000</Version><OtherValues /></Form>
2           <Form><Version>1000</Version><OtherValues /></Form>

Data update:

UPDATE @t
SET X.modify('
replace value of 
    (/Form/Version[.="1000"]/text())[1]
with
    "1001"
')

Post-change data:

SELECT * FROM @t

Id          X
----------- ------------------------------------------------------------
1           <Form><Version>1001</Version><OtherValues /></Form>
2           <Form><Version>1001</Version><OtherValues /></Form>

---

Things to note:

• replace value of requires that the 'to-be-replaced' expression identifies a "statical singleton", ie the parser must be able to work out that it refers to a single value - hence the [1]
• Only one node (per row) will ever be modified by .modify! So if you have multiple XML nodes in a single row, you will have to iterate manually