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!