inserting a node into SQL 2008 xml datatype... checking if it exists first

Question

Hi, I am reading through a plethora of articles at the moment to try to assist me.. just seems so many options and cannot seem to find a clean solution.. it probably is very basic so apologies in advance!

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

<root><id>1</id><id>4</id></root> and so on...

What I am hoping to do is pass a param in to a proc to insert an value IF it doesn't exist..

So rather than read the xml first and do this within say .NET code, is there a clean way of doing this within a stored proc/t-sql ???

Any help appreciated! I am sure this is a fairly common one!

Answer 1

An 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 or not.

Check out An Overview of XML Support in SQL Server 2005 - it's a great article on how to use the various XQuery functions available. Just after the middle of that page, you'll find this section:

Using the exist Method

The exist method takes an XPath expression that selects a single node within the XML document, and returns either True (bit value 1) if the node exists or False (bit value 0) if it does not. If the source column is a typed xml column (in which case you must declare the namespace in your query), and the element contains null, the method returns NULL instead. So the XQuery:

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

will return True if there is a product with the id value "304" (a product element with the attribute id="304"), or False if not. You can also use the exist method in the WHERE clause of a SQL statement:

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