Requêtes XQuery impliquant une hiérarchie
Pour la plupart, les colonnes de type xml de la base de données AdventureWorks sont des documents semi-structurés. Par conséquent, les documents stockés dans chaque ligne peuvent avoir un aspect différent. Les exemples de requêtes fournis dans cette rubrique montrent comment extraire des informations de ces divers documents.
Exemples
A. Extraction, à partir des instructions de fabrication, des postes de travail ainsi que de la première étape de fabrication réalisée sur ces différents postes
Pour le modèle de produit 7, la requête construit un code XML qui inclut l'élément <ManuInstr>, avec ses attributs ProductModelID et ProductModelName, et un ou plusieurs éléments enfants <Location>.
Chaque élément <Location> dispose de son propre ensemble d'attributs et d'un élément enfant <step>. Cet élément enfant <step> représente la première étape de fabrication réalisée sur le poste de travail.
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
Notez les points suivants par rapport à la requête précédente :
- Le mot clé namespace du prologue XQuery définit un préfixe d'espace de noms. Ce préfixe est utilisé ultérieurement dans le corps de la requête.
- Les jetons de basculement de contexte, {) et (}, sont utilisés pour faire passer la requête de la construction XML à sa propre évaluation.
- La fonction sql:column() permet d'inclure une valeur relationnelle dans le code XML en cours d'élaboration.
- Lors de la construction de l'élément <
Location>, $wc/@* récupère tous les attributs des postes de travail. - La fonction string() renvoie la valeur de chaîne de l'élément <
step>.
Voici le résultat partiel :
<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. Recherche de tous les numéros de téléphone de la colonne AdditionalContactInfo
La requête suivante récupère tous les numéros de téléphone supplémentaires définis pour un contact client spécifique en recherchant l'élément <telephoneNumber> dans l'ensemble de la hiérarchie. Dans la mesure où l'élément <telephoneNumber> peut apparaître n'importe où dans la hiérarchie, la requête utilise l'opérateur descendant-and-self (//) dans la recherche.
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
Voici le résultat obtenu :
<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>
Pour récupérer uniquement les numéros de téléphone de premier niveau, et plus particulièrement les éléments enfants <telephoneNumber> de <AdditionalContactInfo>, l'expression FOR de la requête devient
for $ph in /ci:AdditionalContactInfo/act:telephoneNumber.
Voir aussi
Concepts
Concepts de base de XQuery
Construction XML (XQuery)
Type de données xml