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?

For SQL-Server the correct language for update is XML-DML. – Apr 13 '11 at 15:42 — , Apr 13 '11 at 15:42
Noted, changed the question title to reflect that. – Chuck Callebs Apr 13 '11 at 15:43 — Chuck Callebs, Apr 13 '11 at 15:43

score 5 · Accepted Answer · answered Apr 13 '11 at 15:11

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

Worked like a charm. Thanks a bunch, AakashM. – Chuck Callebs Apr 13 '11 at 15:27 — Chuck Callebs, Apr 13 '11 at 15:27

Editing XML From SQL Query Using XML-DML

1 Answers1