SQL 301-XML EXIST Method

And now it’s time to wrap up the XML in SQL posts.  At least, this is the last of the information you’re required to know for the 70-433.  Anything above and beyond this is great…just not required.  Let’s dive right in!

Exist

This first method I want to cover is EXIST.  With this method you can test to see if a certain NODE, Attribute or value exists within the XML stored.  While Searching for elements and nodes are simple, there is some delicate maneuvers required to make the filters work.  I would suggest spending some time in the Books On Line for XPath to get the finer points.

The EXIST method returns a 1 or a 0.  If it finds the path you’ve asked for, you’ll get a 1, otherwise 0.  Simple, right?

Let’s start with a simple test to see if a resume in our system has an Employement Function listed.  We’ll look in the HumanResources.JobCandidate table and run the following query.

   1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

   2: SELECT [Resume]

   3: FROM HumanResources.JobCandidate

   4: WHERE

   5:     [Resume].exist('/Resume/Employment/Emp.FunctionCategory') = 1

The XPath I’m looking for is /Resume/Employment/Emp.FunctionCategory.  In my copy of AdventureWorks I have 13 resumes that have have a Emp.FunctionCategory Defined.

Looking for an attribute would be just as easy, just remember to add @ before the attribute name.

Now that you know how to look at attributes and elements, let’s add some filtering.  Let’s say we wanted to look at all the applicants with a GPA greater than 3.0.

   1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

   2: SELECT [Resume]

   3: FROM HumanResources.JobCandidate

   4: WHERE

   5:     [Resume].exist('/Resume/Education/Edu.GPA[xs:decimal(.) > 3.0]') = 1

   6:

Anything wrapped in square brackets will be your filter.  In this case, I wanted to see GPA’s greater than 3.0.  I did have to force the XML to interpret the values stored in Edu.GPA as decimal, so the comparison would work.  So I added xs:decimal(.) to case the values as a decimal.  Then I added the > 3.0, so that only values greater than 3 would be returned.

You don’t always have to case the values you’re searching for.  Let’s look at resumes for graduates of Iowa universities.

   1: WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')

   2: SELECT [Resume]

   3: FROM HumanResources.JobCandidate

   4: WHERE

   5:     [Resume].exist('/Resume/Education/Edu.Location/Location/Loc.State[. = "IA "]') = 1

Since the values stored in Loc.State (for Edu.Location) are already strings, I didn’t have to convert the values before doing the comparison.

Exists will be your quick check method.  When you just want to make sure you have an element, node or value, before doing an operation with that object, you can do an EXIST check to make sure it’s there.  If it’s not, you skip the operation.

It beats failing out, right?

Next time I’ll cover the VALUE method.  If you have any questions, please send them in.  I’m here to help you understand SQL better!

Tags:

Trackbacks/Pingbacks

  1. SQL301–XML VALUE Method | Shannon Lowder - 20110307

    [...] Last time, I covered the EXIST Method.  This time around I wanted to show you how to pull out values from XML stored in SQL.  Let’s start with what should have been a simple example.  I wanted to pull the first and last name for a job applicant along with their resume in XML format. [...]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.