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>
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 .