How to modify xml subnodes with XQuery on SQL Server

Question

For an xml field (SQL Server) I need to add a node in every subnode, based on a source table and a condition. This is my xml data:

declare @X table (XMLDATA xml)
insert @X values('
<row>
  <node>
    <name>Francesco</name>
  </name>
  <node>
    <label>Alessandro</name>
  </node>
  <node>
    <name>Daniele</name>
  </node>
</row>')

For every <name>, I want to add a node <number>. The matches for numbers and names are written in a table @T:

declare @T table (name varchar(20), number int)
insert @T values
('Alessandro', 24)
,('Francesco', 10)
,('Daniele', 16)

To update the nodes I use XMLDATA.modify, and I use xpath conditions to select the right node:

update @X set XMLDATA.modify('insert element number {sql:column("number")} as last into (row/node[name=sql:column("name")])[1]')
from @X
cross join @T

The query above works only for the first row of @T (in the example is Alessandro/24). The other 2 rows of @T are ignored. I need to add number to every node. This is the final XMLDATA:

<row>
  <node>
    <name>Francesco</name>
  </node>
  <node>
    <name>Alessandro</name>
    <number>24</number>
  </node>
  <node>
    <name>Daniele</name>
  </node>
</row>

Even if answer of Ronak Patel is a good workaround (the which I have evaluted) I would like to know how fieldxml.modify() works and the syntax to update many nodes in a row. Am I wrong, and a workaround that helped me should be marked as "right answer"? — Radioleao
– Radioleao, Commented Feb 16, 2017 at 12:52
Valid point... Below my answer you commented * I simply write a sample subset* (please add expected output fitting to the sample). This would help to give you a precise answer. — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 16, 2017 at 13:02
I wrote that I have hundreds of nodes at different level. It's necessary that I post a real example to ask a MS SQL syntax? — Radioleao
– Radioleao, Commented Feb 20, 2017 at 8:30
Sure! Your question is highly depending on the actual structure of your XML. Please create a (reduced) XML and the expected output. That might be clear for you, but for an external person it isn't. — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 20, 2017 at 8:34

halfer · Accepted Answer · 2017-04-14 14:27:50Z

1

I use while loop. Please check below code, it may be help you.

declare @X table (XMLDATA xml)
insert @X values('
<row>
  <node>
    <name>Alessandro</name>
  </node>
  <node>
    <name>Francesco</name>
  </node>
  <node>
    <name>Daniele</name>
  </node>
</row>')


declare @T table (name1 varchar(20), number int,RowID int identity(1,1) not null)
insert @T values
('Alessandro', 24)
,('Francesco', 10)
,('Daniele', 16)

DECLARE @i int,@iCount int,@namevalue varchar(100)='',@number int
SET @i = 1


SELECT @iCount=MAX(RowID) FROM @T

WHILE (@i <= @iCount)
BEGIN

SELECT @namevalue=name1 FROM @T WHERE RowID=@i
SELECT @number=number FROM @T WHERE RowID=@i

update @X set XMLDATA.modify('insert element number {sql:variable("@number")} as last into (row/node)[name=sql:variable("@namevalue")][1]')
from @X
cross join @T

  SET @i = @i + 1
END

SELECT * FROM @X

Output :

<row>
  <node>
    <name>Alessandro</name>
    <number>24</number>
  </node>
  <node>
    <name>Francesco</name>
    <number>10</number>
  </node>
  <node>
    <name>Daniele</name>
    <number>16</number>
  </node>
</row>

Thanks .

edited Apr 14, 2017 at 14:27

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 7, 2017 at 10:19

Ronak Patel

6405 silver badges15 bronze badges

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

2 Comments

Radioleao Over a year ago

This is a good solution, I like it even more if you use a cursor instead of a loop. I would like to find the right update query without use any loop, but your is a good workaround. Thanks

Radioleao Over a year ago

This is the best solution I got. I was expecting some different syntax to manage with subnodes, but I'm afraid there isn't any

Gottfried Lesigang · Accepted Answer · 2017-02-07 22:05:15Z

0

If your real life data is as simple as the example, the solution can be found much easier:

declare @X table (XMLDATA xml)
insert @X values(
'<row>
  <node>
    <name>Francesco</name>
  </node>
  <node>
    <name>Alessandro</name>
  </node>
  <node>
    <name>Daniele</name>
  </node>
</row>')

declare @T table (name varchar(20), number int)
insert @T values
('Alessandro', 24)
,('Francesco', 10)
,('Daniele', 16);

SELECT nm.value(N'.','nvarchar(max)') AS name
        ,t.number 
FROM @X
CROSS APPLY XMLDATA.nodes(N'/row/node/name') AS A(nm)
INNER JOIN @T AS t ON t.name=nm.value(N'.','nvarchar(max)')
FOR XML PATH('node'),ROOT('row')

If this does not work for you, please provide more realistic sample data!

answered Feb 7, 2017 at 22:05

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

1 Comment

Radioleao Over a year ago

I have hundreds of nodes and many subnodes. I simply write a sample subset, to be as clear as possible.

Collectives™ on Stack Overflow

How to modify xml subnodes with XQuery on SQL Server

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related