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