Add node to XML using TRANSACT-SQL

Question

I've been struggling with this simple piece of code without result. I'm just trying to add a new node to an XML variable.

DECLARE @XML XML;
SET @XML = '<root>
<policyData>
    <txtComentario />
    <idRegProducto>76</idRegProducto>
    <txtDuracion>24</txtDuracion>
</policyData>
</root>';
DECLARE @NODE XML;
SET @NODE = '<newNode>10</newNode>';
SET @XML.modify
('insert sql:variable("@NODE") as first
into (/root/policyData)[0]')
SELECT @XML;

There is no errors, but the new node is not showing in the output. Is there something that I must setup first before using XML in SQL Server? Any suggestion why this is not working?

Thanks in advance!

Try to change [0] to [1]

Mikael Eriksson
– Mikael Eriksson

2013-08-26 15:32:02 +00:00
Commented Aug 26, 2013 at 15:32 — Mikael Eriksson
– Mikael Eriksson, Commented Aug 26, 2013 at 15:32

Mikael Eriksson · Accepted Answer · 2013-08-26 15:39:14Z

9

When you use [0] you are actually saying [position()=0]. The first node has position 1 so you should change the predicate to [1] if you want to insert the new node into the first occurrence of policyData.

answered Aug 26, 2013 at 15:39

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

1 Comment

Oscar Over a year ago

Thanks a lot. I wonder why Microsoft doesn't throw an error here to let the user knows that the syntax is not ok..

Collectives™ on Stack Overflow

Add node to XML using TRANSACT-SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related