Wednesday, February 19, 2014

Homework and answers

So like I suggested for homework querying an XML document I went with the books suggestion which was to use the Resume column in humanresources.jobcandidates table (AdventureWorks).  The suggested practice was to find everyone's first and last name... then the secondary part of the problem was to find all candidates who lived in Chicago (location.city).  Here is the way that I found to do it:

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' AS ns)
SELECT [Resume].value('(/ns:Resume/ns:Name/ns:Name.First)[1]', 'varchar(50)') AS Firstname,
[Resume].value('(/ns:Resume/ns:Name/ns:Name.Last)[1]', 'varchar(50)') AS Lastname
FROM HumanResources.JobCandidate
WHERE [Resume].exist('/ns:Resume/ns:Employment/ns:Emp.Location/ns:Location/ns:Loc.City[.="Chicago"]')=1


I'm going to post this to SSC to see if there is an easier way of doing this (meaning an easier way of writing it, not optimization wise), and what are the search methods when values are attributes instead of elements as the resumes were.

Cheers!

No comments:

Post a Comment