add node if node not exists in xml-xquery

Category: sql server xml

Question

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


Hi 

I have this xml.

declare @xmlp xml='<p>
  <pl>
    <id>6</id>
    <name>aaa</name>
  </pl>
</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


Replies

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'
<p>
  <pl>
    <id>6</id>
    <name>aaa</name>
  </pl>
</p>
' ;

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 );

INSERT INTO @xmlp 
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;