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!
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
.