Loop and modify xml nodes SQL Server

Question

I want to loop through xml nodes in SQL Server, and create a copy of each node containing ','.

For example, for the following xml :

 declare @answerXML xml = '<answers><answer part="1">answer,test0</answer><answer part="1">answer,test1</answer></answers>'

I want to be modified to become the following :

declare @answerXML xml = '<answers><answer part="1">answer,test0</answer><answer part="1">answer, test0</answer><answer part="1">answer,test1</answer><answer part="1">answer, test1</answer></answers>'

(Each node is duplicated, and in the added node, a space is added after the comma).

I was planing in using something like this :

SELECT 
    T.ref.value('.', 'varchar(256)') AS Answer 
FROM 
    (SELECT 
         [Xml] = @answerXML.query('for $i in data(/answers/answer) 
                                   return element temp { $i }')
    ) A
CROSS APPLY
    A.Xml.nodes('/temp') T(ref)

But no use, it seems it's too complicated.

Can anyone help in how to loop and update XML in T-SQL?

Thank you in advance,

Is this the real structure? Why do you need this? What are you trying to solve with this duplication? — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 15, 2017 at 21:01
Yes this is the real structure. It's an xml that stores all possible values that the user can input. So if he inputs answer,test0 or answer, test0, the input should be considered as a valid one. — user3012488
– user3012488, Commented Dec 15, 2017 at 21:06
I think, this design might be better with a mapping table. I'd maintain a table with all valid values and a 1:n related table with all valid aliases. In this case you can join the user's input to your alias table and find the corresponding value. — Gottfried Lesigang
– Gottfried Lesigang, Commented Dec 15, 2017 at 21:14

Gottfried Lesigang · Accepted Answer · 2017-12-15 21:11:25Z

2

You can try to shred the XML and rebuild it from scratch:

declare @answerXML xml=
'<answers>
  <answer part="1">answer,test0</answer>
  <answer part="1">answer,test1</answer>
</answers>';

SELECT a.value(N'@part',N'int') AS [answer/@part]
      ,a.value(N'text()[1]',N'nvarchar(max)') AS [answer] 
      ,''
      ,a.value(N'@part',N'int') AS [answer/@part]
      ,REPLACE(a.value(N'text()[1]',N'nvarchar(max)'),',',', ') AS [answer] 
FROM @answerXML.nodes(N'/answers/answer') AS A(a)
FOR XML PATH(''),ROOT('answers')

The result

<answers>
  <answer part="1">answer,test0</answer>
  <answer part="1">answer, test0</answer>
  <answer part="1">answer,test1</answer>
  <answer part="1">answer, test1</answer>
</answers>

answered Dec 15, 2017 at 21:11

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

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

8 Comments

Jacob H Over a year ago

Or shred the XML and insert into a table then query the table!

Gottfried Lesigang Over a year ago

@JacobH What help / advantage would this bring? Using .nodes() there is a (volatile) derived table already...

user3012488 Over a year ago

Added the folowing condition to check if a comma exists. WHERE CHARINDEX(',',a.value(N'text()[1]',N'nvarchar(max)'))>0. Big Thanks -@Shnugo

Jacob H Over a year ago

The advantage of not having to shred and recreate the XML to store in a column each time you modify the data. But it sounds like you're happy with that process, so carry on!

Gottfried Lesigang Over a year ago

@JacobH This sounds a little - uhm - supercilious... We both don't know, what the OP is doing here. If this is a one time action, if there is any need to keep this data for a second action... The main question is: Is XML the best place for this data at all? But this is the wrong place to discuss this...

|

Collectives™ on Stack Overflow

Loop and modify xml nodes SQL Server

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related