Export (0) Print
Expand All

XQueries Involving Hierarchy

Most xml type columns in the AdventureWorks 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
              return
                <Location>
                 {$wc/@* }
                 <step1> { string( ($wc//AWMI:step)[1] ) } </step1>
                </Location>
            }
          </ManuInstr>
') 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>
   <Location LocationID="20" SetupHours="0.15" 
            MachineHours="2" LaborHours="1.75" LotSize="1">
      <step1>Assemble all frame components following 
             blueprint 1299.</step1>
   </Location>
...
</ManuInstr> 

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
   return
      $ph/act:number
') as x
FROM  Person.Contact
WHERE ContactID = 1

This is the result:

<act:number 
  xmlns:act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
  111-111-1111
</act:number>
<act:number 
  xmlns:act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
  112-111-1111
</act:number>

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

ADD
Show:
© 2014 Microsoft