add node if node not exists in xml-xquery

Category: sql server xml


Chakradhar Masineni on Fri, 01 Nov 2013 12:19:40


I have this xml.

declare @xmlp xml='<p>
select @xmlp 

in this xml if <name> node not exists i should add <name> node under <pl> node. 

how can we do that using xquery ?

thanks in advance


Kalman Toth on Fri, 01 Nov 2013 12:24:02

I am moving it to XML.

Stefan Hoffmann on Fri, 01 Nov 2013 13:24:35

Use modify() with insert:

DECLARE @xmlp XML = N'
' ;

SET @xmlp.modify('
	insert <name2>bbb</name2> 
	as last 
	into (/p/pl)[1]

SELECT @xmlp;

And as a table modifcation use also exist():

DECLARE @xmlp TABLE ( [Data] XML );

VALUES	( N'<p><pl><id>1</id><name>aaa</name></pl></p>' ),
		( N'<p><pl><id>2</id></pl></p>' ); 

UPDATE	@xmlp
SET		[Data].modify('
			insert <name>bbb</name> 
			as last 
			into (/p/pl)[1]
WHERE	[Data].exist('/p/pl/name') = 0;

SELECT * FROM @xmlp;