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 Aug 26 '13 at 15:32 — Mikael Eriksson, Aug 26 '13 at 15:32

score 9 · Accepted Answer · answered Aug 26 '13 at 15:39

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 '13 at 15:39

Mikael Eriksson

136,425
22
210
281

Thanks a lot. I wonder why Microsoft doesn't throw an error here to let the user knows that the syntax is not ok.. – Oscar Aug 26 '13 at 15:45

Add node to XML using TRANSACT-SQL

1 Answers1