Insert into xml in sql using modify method

Question

I have to modify the existing xml in a table field value. Each row value has different xml tags like

1 row. '<root><comments><comment>comments1</comment><comment>comments2</comment></comments></root>'

2 row .

'<Users><User><Name>MAK</Name></User><User><Name>DANNY</User></Users>'

I need to add a tag <Resource>some ID</Resource> after the root node.

like '<Users>**<Resource>some ID</Resource>**<User><Name>comments1</Name></User><User><Name>comments2</User></Users>'

I have tried with the below code .

declare @xml xml
set @xml = '<root><comments><comment>comments1</comment><comment>comments2</comment></comments></root>'
declare @Note varchar(10)
declare @insertnode nvarchar(100)
set @insertnode='commeressd'
declare @mainnode varchar(50)
set @mainnode='(//root)[1]'
set @Note = 'comment3'
SET @xml.modify('insert <Resource>{xs:string(sql:variable("@insertnode"))}</Resource> as first into {xs:string(sql:variable("@mainnode"))}')
select @xml

the expression after

into

is giving the error

XQuery [modify()]: Syntax error near '{'

..how do we specify this into expression also dynamically.

Any help would be appreciated.

Mikael Eriksson · Accepted Answer · 2014-05-16 12:46:56Z

3

You can use /*[1] to find the "first" root node where you want the insert to happen.

declare @xml xml
set @xml = '
<root>
  <comments>
    <comment>comments1</comment>
    <comment>comments2</comment>
  </comments>
</root>'

declare @insertnode nvarchar(100)

set @insertnode='ResourceID'

set @xml.modify('insert element Resource {sql:variable("@insertnode")} as first into /*[1]')

select @xml

Result:

<root>
  <Resource>ResourceID</Resource>
  <comments>
    <comment>comments1</comment>
    <comment>comments2</comment>
  </comments>
</root>

answered May 16, 2014 at 12:46

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

Collectives™ on Stack Overflow

Insert into xml in sql using modify method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related