XQueries Involving Hierarchy

Most xml type columns in the AdventureWorks2008R2 database are semi-structured documents. Therefore, documents stored in each row may look different. The query samples in this topic illustrate how to extract information from these various documents.

A. From the manufacturing instructions documents, retrieve work center locations together with the first manufacturing step at those locations

For product model 7, the query constructs XML that includes the <ManuInstr> element, with ProductModelID and ProductModelName attributes, and one or more <Location> child elements.

Each <Location> element has its own set of attributes and one <step> child element. This <step> child element is the first manufacturing step at the work center location.

SELECT Instructions.query('
     declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
   <ManuInstr  ProdModelID = "{sql:column("Production.ProductModel.ProductModelID") }" 
                ProductModelName = "{ sql:column("Production.ProductModel.Name") }" >
              for $wc in //AWMI:root/AWMI:Location
                 {$wc/@* }
                 <step1> { string( ($wc//AWMI:step)[1] ) } </step1>
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7;

Note the following from the previous query:

  • The namespace keyword in the XQuery Prolog defines a namespace prefix. This prefix is used later in the query body.

  • The context switching tokens, {) and (}, are used to switch the query from XML construction to query evaluation.

  • The sql:column() is used to include a relational value in the XML that is being constructed.

  • In constructing the <Location> element, $wc/@* retrieves all the work center location attributes.

  • The string() function returns the string value from the <step> element.

This is a partial result:

<ManuInstr ProdModelID="7" ProductModelName="HL Touring Frame">
   <Location LocationID="10" SetupHours="0.5" 
            MachineHours="3" LaborHours="2.5" LotSize="100">
     <step1>Insert aluminum sheet MS-2341 into the T-85A 
             framing tool.</step1>
   <Location LocationID="20" SetupHours="0.15" 
            MachineHours="2" LaborHours="1.75" LotSize="1">
      <step1>Assemble all frame components following 
             blueprint 1299.</step1>

B. Find all telephone numbers in the AdditionalContactInfo column

The following query retrieves additional telephone numbers for a specific customer contact by searching the whole hierarchy for the <telephoneNumber> element. Because the <telephoneNumber> element can appear anywhere in the hierarchy, the query uses the descendant and self operator (//) in the search.

SELECT AdditionalContactInfo.query('
 declare namespace ci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
 declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
for $ph in /ci:AdditionalContactInfo//act:telephoneNumber
') as x
FROM  Person.Person
WHERE BusinessEntityID = 291;

This is the result:


To retrieve only the top-level telephone numbers, specifically the <telephoneNumber> child elements of <AdditionalContactInfo>, the FOR expression in the query changes to

for $ph in /ci:AdditionalContactInfo/act:telephoneNumber.

Community Additions