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
– Ajay2707, Commented Aug 11, 2015 at 9:59

Mikael Eriksson · Accepted Answer · 2015-08-11 11:12:04Z

2

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

answered Aug 11, 2015 at 11:12

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Comments

Community · Accepted Answer · 2017-05-23 12:06:13Z

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, 2017 at 12:06

CommunityBot

11 silver badge

answered Aug 11, 2015 at 8:14

Julien Vavasseur

3,9701 gold badge22 silver badges30 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related