SQLXML : How to Use SQL Server XML Function exist()

Written by belle
Posted April 8, 2009 at 11:14 pm

-- ============================================================
-- sample code on using the SQL Server xml method exist()
-- several samples shown
-- Donabel Santos
-- ============================================================
DECLARE @xmlSnippet XML
DECLARE @id SMALLINT
DECLARE @value VARCHAR(20)
SET @xmlSnippet =
'
<ninjaElement id="1">SQL Server Ninja</ninjaElement>
<ninjaElement id="2">SharePoint Ninja</ninjaElement>
<ninjaElement id="3">ASP.NET Ninja</ninjaElement>
'
-- this is what we will look for
SET @id    = 2
SET @value ='SQL Server Ninja'
-- note exist() will return only either :
-- 1 (true) or 0 (false)
-- check if a node called ninjaElement exists
-- at any level in the XML snippet
SELECT @xml.exist('//ninjaElement')
-- check if a node called bar exists
SELECT @xml.exist('//bar')
-- check if attribute id exists anywhere
SELECT @xml.exist('//@id')
-- check if attribute id exists within a ninjaElement tag
SELECT @xml.exist('//ninjaElement[@id]')
-- check if the id attribute equals to what we saved
-- in the @id variable
SELECT @xml.exist('/ninjaElement[@id=sql:variable("@id")]')
-- check if the node text equals to what
-- we saved in the @value variable
SELECT @xml.exist('/ninjaElement[text()=sql:variable("@value")]')

Share :

Related posts:

Filed under: SQLXML

4 Responses to this post

timber trade marketplace on April 23rd, 2009 7:28 am

Hi, how can use xml in join clause? e.g. I have a xml variable and want to make something like this

table t1
join @XML.nodes(’/root/id’) as ParamValues(ID)
on t1.id=ParamValues.ID.value(’.’,’int’)

??
Fred on June 24th, 2009 1:28 pm

great post…saved me a lot of time!!!!
Andrew on July 16th, 2009 7:43 pm

Found a small problem … you declare:

DECLARE @xmlSnippet XML

And then assign:

SET @xmlSnippet =

But in the queries you refer to @xml, which doesn’t exist:

SELECT @xml.exist('//ninjaElement')
Ben on July 16th, 2010 2:07 pm

Oh, whoa, this saved me from a few gray hairs. I was trying to get the value of an XML element that existed in some columns, but not all, and adding it with similar elements in the same column (quantity1, quantity2, etc). In some instances, quantity1 didn’t exist, yet quantity2 or quantity3 would, but the whole addition statement would be null. Now I check if the element exists; if it doesn’t, the case statement returns 0; otherwise, it returns the value of the element.

Thank you!