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

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

— ============================================================
— 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")]’)

[/sql]

9 thoughts on “SQLXML : How to Use SQL Server XML Function exist()

  1. timber trade marketplace April 23, 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’)

    ??

    Like

  2. Fred June 24, 2009 / 1:28 pm

    great post…saved me a lot of time!!!!

    Like

  3. Andrew July 16, 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')

    Like

  4. Ben July 16, 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!

    Like

  5. krishnaroopa November 23, 2010 / 3:43 am

    Good Sample. In the select statement,
    SELECT @xml.exist(‘/ninjaElement[@id=sql:variable(“@id”)]’)

    can I give the condition itself in a variable? Have you tried out?

    Like

    • belle November 24, 2010 / 9:56 am

      yeah i did. unfortunately that doesn’t work.

      Like

  6. Pingback: SQL Server Central
  7. Gerald L Bryant March 1, 2012 / 1:21 pm

    Passing in the whole criteria to .exist() as a variable doesn’t work as she said. What does work is building the SQL for use in a call to sp_ExecuteSQL. It’s the only way around it I’ve found. I have a generic content table that has an Xml column to hold specific content properties serialized from our C# app. With that change from multiple specific content tables to one generic content table still comes the desire to filter and sort on values in the Xml column for any given content type as known by the business layer of the app. The idea is to pass to a ReadContentByCriteria an optional parameter that can be used in an .exist() on ContentPropertiesXml column. I was dismayed and annoyed at the inflexibility in having to have a literal or sql:variable[] as the main choices. So I ended up concatenating the Results temp table with @PropertiesXQuery as part of the WHERE clause. It’s a hack but it works.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s