how to add a XML Node inside all nodes in sql server

Question

I have a column in sql database with type XML. I want to add a new node EFDRow with same value in all nodes with name GoodsSerial.

<Serials>
  <GoodsSerial>
    <GSerialSCredit>2015/08/11</GSerialSCredit>
    <GSerialECredit>2015/08/11</GSerialECredit>
    <GSerialStart>1</GSerialStart>
    <GSerialEnd>5</GSerialEnd>
  </GoodsSerial>
  <GoodsSerial>
    <GSerialSCredit>2015/08/11</GSerialSCredit>
    <GSerialECredit>2015/08/11</GSerialECredit>
    <GSerialStart>10</GSerialStart>
    <GSerialEnd>15</GSerialEnd>
  </GoodsSerial>
</Serials>

I have tried this Code

UPDATE Tbl
SET SerialXml.modify('insert <EFDRow>{sql:column("EFDRow")}</EFDRow> into (/Serials/GoodsSerial)[1]')

But it adds the EFDRow node only to first GoodsSerial node.

its not clearly understand. Can you show, what will be the output? update your que. — Ajay2707, Aug 11 '15 at 09:59
possible duplicate of [xml-sql: update multiple nodes](http://stackoverflow.com/questions/16712841/xml-sql-update-multiple-nodes) — har07, Aug 11 '15 at 11:22

score 2 · Accepted Answer · answered Aug 11 '15 at 11:12

You can shred your XML no /Serials/GoodsSerial and the rebuild it with the value of EFDRow added using for xml path.

update dbo.Tbl
set SerialXml = (
                select GS.X.query('*'),
                       EFDRow
                from SerialXml.nodes('/Serials/GoodsSerial') as GS(X)
                for xml path('GoodsSerial'), root('Serials'), type
                );

GS.X.query('*') will give you all the nodes that already exist in GoodsSerial.

SQL Fiddle

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

Modify can only replace 1 value at a time. You must loop through all node:

Updating all nodes by condition with xml to sql

xml-sql: update multiple nodes

edited May 23 '17 at 12:06

Community

1
1

answered Aug 11 '15 at 08:14

Julien Vavasseur

3,854
1
20
29

how to add a XML Node inside all nodes in sql server

2 Answers2