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.
Examples
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="https://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="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="https://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="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes">
111-111-1111
</act:number>
<act:number
xmlns:act="https://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.