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!

Answer 1

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.