Parse XML using T-SQL and XQUERY - using a condition statement

Question

Microsoft practice database called AdvantureWorks2012 has a table called HumanResources.JobCandidate. That table has several columns. One of those columns is of a uri datatype. This is what's inside one of the cells in that column:

<ns:Resume xmlns:ns="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume">
  <ns:Name>
    <ns:Name.Prefix></ns:Name.Prefix>
    <ns:Name.First>Shai</ns:Name.First>
    <ns:Name.Middle></ns:Name.Middle>
    <ns:Name.Last>Bassli</ns:Name.Last>
    <ns:Name.Suffix></ns:Name.Suffix>
  </ns:Name>
  <ns:Skills>
I am an experienced and versatile machinist who can operate a range of machinery personally as well as supervise the work of other machinists. I specialize in diagnostics and precision inspection, have expertise in reading blueprints, and am able to call on strong interpersonal and communication skills to guide the work of other production machinists whose work I am called upon to inspect. 
My degree in mechanical engineering affords me a better theoretical understanding and mathematical background than many other candidates in the machinist trade.
    </ns:Skills>
  <ns:Employment>
    <ns:Emp.StartDate>2000-06-01Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>2002-09-30Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>Wingtip Toys</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Lead Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility> Supervised work of staff of four machinists. Coordinated all complex assembly and tooling activities, including production of tricycles and wagons.
Developed parts fabrication from sample parts, drawings and verbal orders.Worked with ISO9000 implementation.
        </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>MI </ns:Loc.State>
        <ns:Loc.City>Saginaw</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Employment>
    <ns:Emp.StartDate>1996-11-15Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>2000-05-01Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>Blue Yonder Airlines</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility>Repaired and maintained a variety of production and fabrication machine tools.
Set up and operated machines to close tolerances. Used and wrote CNC machine programs. Trained extensively in computer-aided manufacturing.
        </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IL </ns:Loc.State>
        <ns:Loc.City>Chicago</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Employment>
    <ns:Emp.StartDate>1994-06-10Z</ns:Emp.StartDate>
    <ns:Emp.EndDate>1996-07-22Z</ns:Emp.EndDate>
    <ns:Emp.OrgName>City Power and Light</ns:Emp.OrgName>
    <ns:Emp.JobTitle>Assistant Machinist</ns:Emp.JobTitle>
    <ns:Emp.Responsibility>Performed centerless grinding. Received training in manual mill and lathe machines, as well as micrometers and calipers.
Owned complete toolset.Worked extensive overtime on request. </ns:Emp.Responsibility>
    <ns:Emp.FunctionCategory>Production</ns:Emp.FunctionCategory>
    <ns:Emp.IndustryCategory>Manufacturing</ns:Emp.IndustryCategory>
    <ns:Emp.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IA </ns:Loc.State>
        <ns:Loc.City>Des Moines</ns:Loc.City>
      </ns:Location>
    </ns:Emp.Location>
  </ns:Employment>
  <ns:Education>
    <ns:Edu.Level>Bachelor</ns:Edu.Level>
    <ns:Edu.StartDate>1990-09-15Z</ns:Edu.StartDate>
    <ns:Edu.EndDate>1994-05-10Z</ns:Edu.EndDate>
    <ns:Edu.Degree>Bachelor of Science</ns:Edu.Degree>
    <ns:Edu.Major>Mechanical Engineering</ns:Edu.Major>
    <ns:Edu.Minor></ns:Edu.Minor>
    <ns:Edu.GPA>3.2</ns:Edu.GPA>
    <ns:Edu.GPAScale>4</ns:Edu.GPAScale>
    <ns:Edu.School>Midwest State University</ns:Edu.School>
    <ns:Edu.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>IA </ns:Loc.State>
        <ns:Loc.City>Ames</ns:Loc.City>
      </ns:Location>
    </ns:Edu.Location>
  </ns:Education>
  <ns:Address>
    <ns:Addr.Type>Home</ns:Addr.Type>
    <ns:Addr.Street>567 3rd Ave</ns:Addr.Street>
    <ns:Addr.Location>
      <ns:Location>
        <ns:Loc.CountryRegion>US </ns:Loc.CountryRegion>
        <ns:Loc.State>MI </ns:Loc.State>
        <ns:Loc.City>Saginaw</ns:Loc.City>
      </ns:Location>
    </ns:Addr.Location>
    <ns:Addr.PostalCode>53900</ns:Addr.PostalCode>
    <ns:Addr.Telephone>
      <ns:Telephone>
        <ns:Tel.Type>Voice</ns:Tel.Type>
        <ns:Tel.IntlCode>1</ns:Tel.IntlCode>
        <ns:Tel.AreaCode>276</ns:Tel.AreaCode>
        <ns:Tel.Number>555-0114</ns:Tel.Number>
      </ns:Telephone>
      <ns:Telephone>
        <ns:Tel.Type>Fax</ns:Tel.Type>
        <ns:Tel.IntlCode>1</ns:Tel.IntlCode>
        <ns:Tel.AreaCode>276</ns:Tel.AreaCode>
        <ns:Tel.Number>555-0132</ns:Tel.Number>
      </ns:Telephone>
    </ns:Addr.Telephone>
  </ns:Address>
  <ns:EMail>[email protected]</ns:EMail>
  <ns:WebSite></ns:WebSite>
</ns:Resume>

I would like to extract those rows that are in Chicago (path to city is /Resume/Employment/Emp.Location/Location/Loc.City) using tsql. I am just a beginner, so an elaborate explanation would go a long way. Thanks.

You asked this question and got an answer. I assume you have tried what you learned there using namespaces and the value function. What issues do you have using that? Why is that not doing what you want here? — Mikael Eriksson
– Mikael Eriksson, Commented Sep 11, 2014 at 17:01
@MikaelEriksson first question asked: find all first and last names in this column. As stated by you, I have received an elaborated answer and understood the syntax. The second question asked: find all candidates from Chicago. I tried for quite a while to play with the code provided to me by the answer for the first question, but I couldn't make it work. I realised that I don't know how to write a condition statement and that's what lead me to post this second question. Thanks. — Paul Kar.
– Paul Kar., Commented Sep 11, 2014 at 17:19
I have seen in some places condition statements, but that was using the query function. I tried using the query function as well, but had no luck. — Paul Kar.
– Paul Kar., Commented Sep 11, 2014 at 17:21
I recommend that you have a look at the Stairway to XML article series at SQL Server Central written by Robert Sheldon. It covers all you need to know to get you started using XML in SQL Server. — Mikael Eriksson
– Mikael Eriksson, Commented Sep 11, 2014 at 17:41

Mikael Eriksson · Accepted Answer · 2014-09-11 17:34:33Z

To check for the existence of nodes in XML you should use exist() Method (xml Data Type). The parameter to exist() is an XQuery that specifies the nodes you want to exist in the XML and it returns 1 if there exist at least one node and 0 if there are no matching nodes..

To check for values in XML you write the predicate within brackets [].

This will give you all Location nodes /Resume/Employment/Emp.Location/Location and to get only the nodes where Loc.City is Chicago you add a predicate [Loc.City = "Chicago"]. The XQuery then becomes /Resume/Employment/Emp.Location/Location[Loc.City = "Chicago"].

This query will give you all candidates that at one time had an employment in Chicago.

with xmlnamespaces(default 'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume')
select *
from HumanResources.JobCandidate as C
where C.Resume.exist('/Resume/Employment/Emp.Location/Location[Loc.City = "Chicago"]') = 1

Collectives™ on Stack Overflow

Parse XML using T-SQL and XQUERY - using a condition statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related