Inserting node into xml x XML data type ... checking to see if it exists first

Question

I am reading a lot of articles at the moment to try to help me .. it just seems that there are so many options and it seems that it cannot find a clean solution. Perhaps this is very simple, so apologize in advance!

So, I have an XML field in SQL 2008. It basically contains something like:

<root><id>1</id><id>4</id></root> etc.

What I hope to do is pass the parameter to proc to insert the value if it does not exist.

So, instead of reading xml first and doing it in .NET code, is there a clean way to do this in saved proc / t-sql ???

Any help appreciated! I am sure this is quite common!

Answer 1

Example using the value () method :

DECLARE 
  @x xml, 
  @param int

SET @x = '<root><id>1</id><id>2</id><id>3</id></root>'
SET @param = 1

IF NOT EXISTS (
  SELECT * FROM @x.nodes('/root/id') n(x) WHERE x.value('.','int') = @param
) 
PRINT 'Insert'
ELSE 
PRINT 'Return'

Answer 2

You can use the .exist()XQuery function on your XML to find out if a given node exists.

Check out XML Support Overview in SQL Server 2005 is a great article on how to use the various XQuery features. Only after the middle of this page you will find this section:

Using an existing method

XPath , node XML True ( 1), node False ( 0), . xml ( ), null, NULL. XQuery:

SELECT MyXml.exist('(/root/product[@id="304"])[1]' FROM MyTable

True, "304" ( id = "304" ), False, . WHERE SQL:

SELECT column1, column2, column3 FROM MyTable
WHERE MyXml.exist('(/root/product[@id="304"])[1]') = 1