Modify multiple rows of SQL Server XML data

Question

I have a temp table that contains values I want injected as elements into a SQL Server column. I can do this for one entry using the modify and insert statements. But how can I do this for multiple rows in a table?

Some sample data looks like this:

SerializedTable:

ID    SerializedXML
-----------------------------------------
7     <Form> <Field1>111</Field1> </Form>
8     <Form> <Field1>112</Field1> </Form>

#TempTable:

ID    FK_ID    Value
--------------------
1     7        120
2     8        124

So I need to add a new element to SerializedXML with the values in the value column.

So the final table would look like:

SerializedTable:

ID    SerializedXML
---------------------------------------------------------------
7     <Form> <Field1>111</Field1> <Field2>120</Field2> </Form>
8     <Form> <Field1>112</Field1> <Field2>124</Field2> </Form>

The name of the element will be the same for all of the new inserted fields.

Alexander Petrov · Accepted Answer · 2015-12-11 02:38:33Z

3

Try this

update s
set SerializedXML.modify('
  insert <Field2>{ sql:column("t.Value") }</Field2>
  after (/Form/Field1)[1] 
')
from SerializedTable s
join TempTable t on s.ID = t.FK_ID

SQL fiddle

answered Dec 11, 2015 at 2:38

Alexander Petrov

14.4k2 gold badges23 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Modify multiple rows of SQL Server XML data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related