Utilisation du mode AUTO
Comme indiqué dans la rubrique Extraction de documents XML à l'aide de FOR XML, le mode AUTO renvoie les résultats de requête sous la forme d'éléments XML imbriqués. Cela ne permet pas de contrôler de façon précise la forme du document XML généré à partir du résultat d'une requête. Les requêtes au mode AUTO sont utiles pour générer des hiérarchies simples. Toutefois, I'utilisation du mode EXPLICIT et l'utilisation du mode PATH permettent de définir la forme du document XML issu du résultat d'une requête avec davantage de contrôle et de souplesse.
Chaque table de la clause FROM, dont au moins une colonne est répertoriée dans la clause SELECT, est représentée sous la forme d'un élément XML. Les colonnes répertoriées dans la clause SELECT sont mappées avec des attributs ou des sous-éléments si l'option ELEMENTS facultative est spécifiée dans la clause FOR XML.
La hiérarchie XML, imbrication des éléments, obtenue dans les données XML dépend de l'ordre des tables définies par les colonnes indiquées dans la clause SELECT. Par conséquent, l'ordre dans lequel les noms de colonnes sont spécifiés dans la clause SELECT est significatif. La première table identifiée, celle qui se trouve le plus à gauche, constitue l'élément du plus haut niveau dans le document XML résultant. La deuxième table la plus à gauche, identifiée par des colonnes dans l'instruction SELECT, constitue un sous-élément de l'élément de plus haut niveau et ainsi de suite.
Si un nom de colonne répertorié dans la clause SELECT provient d'une table déjà identifiée par une colonne précédemment spécifiée dans cette clause, la colonne est ajoutée en tant qu'attribut de l'élément déjà créé et aucun nouveau niveau de hiérarchie n'est ouvert. Si l'option ELEMENTS est spécifiée, la colonne est ajoutée en tant qu'attribut.
Exécutez par exemple cette requête :
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO
Voici le résultat partiel :
<Cust CustomerID="1" CustomerType="S">
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="44501" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="45283" Status="5" />
<OrderHeader CustomerID="1" SalesOrderID="46042" Status="5" />
</Cust>
...
Notez les points suivants relatifs à la clause SELECT :
- L'identificateur CustomerID référence la table Cust. Par conséquent, un élément <
Cust> est créé et un attribut CustomerID y est ajouté. - Ensuite, trois colonnes, OrderHeader.CustomerID, OrderHeader.SaleOrderID et OrderHeader.Status, référencent la table OrderHeader. Par conséquent, un élément <
OrderHeader> est ajouté en tant que sous-élément de l'élément <Cust> et les trois colonnes sont ajoutées en tant qu'attributs de <OrderHeader>. - Ensuite, la colonne Cust.CustomerType référence de nouveau la table Cust déjà identifiée par la colonne Cust.CustomerID. Aucun nouvel élément n'est donc créé. À la place, l'attribut CustomerType est ajouté à l'élément <
Cust> précédemment créé. - La requête spécifie des alias pour les noms de tables. Ces alias apparaissent sous la forme de noms d'éléments correspondants.
- La clause ORDER BY est requise pour regrouper tous les enfants situés sous un même parent.
La requête suivante est similaire à la précédente, sauf que la clause SELECT spécifie des colonnes de la table OrderHeader avant les colonnes de la table Cust. Par conséquent, un premier élément <OrderHeader> est créé, auquel est ensuite ajouté l'élément enfant <Cust>.
select OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerID,
Cust.CustomerType
from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
where Cust.CustomerID = OrderHeader.CustomerID
for xml auto
Voici le résultat partiel :
<OrderHeader CustomerID="1" SalesOrderID="43860" Status="5">
<Cust CustomerID="1" CustomerType="S" />
</OrderHeader>
...
Si l'option ELEMENTS est ajoutée à la clause FOR XML, des données XML centrées sur l'élément sont renvoyées.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
OrderHeader.Status,
Cust.CustomerType
FROM Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader
WHERE Cust.CustomerID = OrderHeader.CustomerID
ORDER BY Cust.CustomerID
FOR XML AUTO, ELEMENTS
Voici le résultat partiel :
<Cust>
<CustomerID>1</CustomerID>
<CustomerType>S</CustomerType>
<OrderHeader>
<CustomerID>1</CustomerID>
<SalesOrderID>43860</SalesOrderID>
<Status>5</Status>
</OrderHeader>
...
</Cust>
...
Dans cette requête, les valeurs CustomerID sont comparées d'une ligne à l'autre lors de la création des éléments <Cust> car CustomerID est la clé primaire de la table. Si CustomerID n'est pas identifié en tant que clé primaire de la table, toutes les valeurs de colonnes (CustomerID et CustomerType dans cette requête) sont comparées d'une ligne à l'autre. Si les valeurs diffèrent, un nouvel élément <Cust> est ajouté au document XML.
Lors de la comparaison de ces valeurs de colonnes, si l'une des colonnes à comparer est de type text, ntext, image ou xml, la clause FOR XML considère que les valeurs sont différentes et non comparées, même si elles peuvent être identiques. Cela est dû au fait que la comparaison des objets volumineux n'est pas prise en charge. Des éléments sont ajoutés au résultat pour chaque ligne sélectionnée. Les colonnes de type (n)varchar(max) et varbinary(max) sont comparées.
À l'image d'une colonne d'agrégation ou calculée, lorsqu'une colonne figurant dans la clause SELECT ne peut être associée à aucune des tables identifiées dans la clause FROM, elle est ajoutée au document XML dans le niveau d'imbrication le plus profond dans la liste. Si une telle colonne apparaît comme première colonne dans la clause SELECT, elle est ajoutée à l'élément supérieur.
Si le caractère générique astérisque « * » est spécifié dans la clause SELECT, l'imbrication est déterminée de la même façon que celle précédemment décrite, en fonction de l'ordre dans lequel les lignes sont renvoyées par le moteur de requête.
Si l'option BINARY BASE64 est spécifiée dans la requête, les données binaires sont renvoyées dans un format codé en base 64. Par défaut, si l'option BINARY BASE64 n'est pas spécifiée, le mode AUTO prend en charge le codage URL des données binaires. Dans ce cas, au lieu de données binaires, une référence à une URL relative vers la racine virtuelle de la base de données dans laquelle la requête est exécutée est renvoyée. Cette référence permet d'accéder aux données binaires réelles lors les opérations ultérieures à l'aide de la requête dbobject SQLXML ISAPI. La requête doit fournir suffisamment d'informations, telles que des colonnes de clé primaire, pour identifier l'image.
Lors de la spécification d'une requête, si un alias est utilisé pour la colonne binaire de la vue, il est renvoyé dans le codage URL des données binaires. Dans les opérations suivantes, l'alias ne signifie rien et le codage URL ne peut pas être utilisé pour extraire l'image. Par conséquent, n'utilisez pas d'alias lors de l'interrogation d'une vue à l'aide du mode FOR XML AUTO.
Présentation des heuristiques du mode AUTO permettant de définir la forme des données XML renvoyées
Le mode AUTO détermine la forme des données XML renvoyées en fonction de la requête. Lors de la définition de l'imbrication des éléments, les heuristiques du mode AUTO comparent les valeurs de colonnes de lignes adjacentes. Les colonnes de tous les types, sauf ntext, text, image et xml, sont comparées. Les colonnes de type (n)varchar(max) et varbinary(max) sont comparées.
L'exemple suivant illustre les heuristiques du mode AUTO qui déterminent la forme des données XML obtenues :
SELECT T1.Id, T2.Id, T1.Name
FROM T1, T2
WHERE ...
FOR XML AUTO
ORDER BY T1.Id
Pour déterminer à quel endroit commence un nouvel élément <T1>, toutes les valeurs de colonne de T1, sauf ntext, text, image et xml, sont comparées si la clé de la table T1 n'est pas spécifiée. Ensuite, supposons que la colonne Name soit de type nvarchar(40) et que l'instruction SELECT renvoie l'ensemble de lignes suivant :
T1.Id T1.Name T2.Id
-----------------------
1 Andrew 2
1 Andrew 3
1 Nancy 4
Les heuristiques du mode AUTO comparent toutes les valeurs de la table T1, les colonnes Id et Name. Étant donné que les deux premières lignes contiennent les mêmes valeurs pour les colonnes Id et Name, un élément <T1> possédant deux éléments enfants <T2> est ajouté au résultat.
Voici le document XML renvoyé :
<T1 Id="1" Name="Andrew">
<T2 Id="2" />
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T>
Supposons maintenant que la colonne Name soit de type text. Les heuristiques du mode AUTO ne comparent pas les valeurs de ce type. À la place, il considère que les valeurs ne sont pas identiques. Cela aboutit à la génération de données XML suivante :
<T1 Id="1" Name="Andrew" >
<T2 Id="2" />
</T1>
<T1 Id="1" Name="Andrew" >
<T2 Id="3" />
</T1>
<T1 Id="1" Name="Nancy" >
<T2 Id="4" />
</T1>
Exemples
Les exemples suivants illustrent l'utilisation du mode AUTO. Nombre de ces requêtes sont spécifiées par rapport aux documents XML des instructions de fabrication de bicyclettes stockés dans la colonne Instructions de la table ProductModel. Pour plus d'informations sur les instructions XML, consultez Représentation du type de données xml dans la base de données AdventureWorks.
A. Extraction des informations sur les clients, les commandes et les détails des commandes
Cette requête extrait les informations de client, de commande et de détail de commande relatives à un client spécifique.
SELECT Cust.CustomerID,
OrderHeader.CustomerID,
OrderHeader.SalesOrderID,
Detail.SalesOrderID, Detail.LineTotal,Detail.ProductID,
Product.Name,
Detail.OrderQty
FROM Sales.Customer Cust,
Sales.SalesOrderHeader OrderHeader,
Sales.SalesOrderDetail Detail,
Production.Product Product
WHERE Cust.CustomerID = OrderHeader.CustomerID
AND OrderHeader.SalesOrderID = Detail.SalesOrderID
AND Detail.ProductID = Product.ProductID
AND (Cust.CustomerID=117 or Cust.CustomerID=442)
ORDER BY OrderHeader.CustomerID,
OrderHeader.SalesOrderID
FOR XML AUTO
Étant donné que la requête identifie des alias de table Cust, OrderHeader, Detail et Product, des éléments correspondants sont générés par le mode AUTO. Là encore, l'ordre dans lequel les tables sont identifiées par les colonnes spécifiées dans la clause SELECT détermine la hiérarchie de ces éléments.
Le résultat partiel est le suivant.
<Cust CustomerID="117">
<OrderHeader CustomerID="117" SalesOrderID="43660">
<Detail SalesOrderID="43660" LineTotal="874.794000" ProductID="758" OrderQty="1">
<Product Name="Road-450 Red, 52" />
</Detail>
<Detail SalesOrderID="43660" LineTotal="419.458900" ProductID="762" OrderQty="1">
<Product Name="Road-650 Red, 44" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="47660">
<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">
<Product Name="Road-650 Black, 58" />
</Detail>
</OrderHeader>
<OrderHeader CustomerID="117" SalesOrderID="49857">
<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">
<Product Name="Women's Tights, S" />
</Detail>
</OrderHeader>
...
</Cust>
B. Spécification de la clause GROUP BY et de fonctions d'agrégation
La requête suivante renvoie des ID de client spécifiques et le nombre de commandes passées par chaque client.
SELECT I.CustomerID, count(*) as NoOfOrders
from Sales.Individual I,Sales.SalesOrderHeader SOH
WHERE I.CustomerID = SOH.CustomerID
GROUP BY I.CustomerID
FOR XML AUTO
Voici le résultat partiel :
<I CustomerID="11000" NoOfOrders="3" />
<I CustomerID="11001" NoOfOrders="3" />
...
C. Spécification de colonnes calculées dans le mode AUTO
Cette requête renvoie des noms concaténés de clients spécifiques et les informations de commande. Étant donné que la colonne calculée est affectée au niveau le plus profond rencontré à ce stade, l'élément <SOH> est utilisé dans cet exemple. Les noms concaténés des clients sont ajoutés comme attributs de l'élément <SOH> dans le résultat.
select C.FirstName + ' ' + C.LastName as Name,
SOH.SalesOrderID
from Sales.Individual I, Person.Contact C,
Sales.SalesOrderHeader SOH
where I.ContactID = C.ContactID
AND I.CustomerID = SOH.CustomerID
FOR XML AUTO
Voici le résultat partiel :
<SOH Name="David Robinett" SalesOrderID="53647" />
<SOH Name="Rebecca Robinson" SalesOrderID="72188" />
Pour extraire les éléments <IndividualCustomer> dont l'attribut Name possède chaque information d'en-tête de commande en tant que sous-élément, la requête est réécrite à l'aide d'un argument sub select. La sélection interne crée une table IndividualCustomer temporaire dans laquelle figure la colonne calculée qui contient les noms des différents clients. Cette table est ensuite jointe à la table SalesOrderHeader afin que soit obtenu le résultat.
La table Sales.Individual stocke des informations sur les différents clients, notamment la valeur ContactID de chacun d'eux. Cette valeur ContactID permet ensuite de rechercher le nom de contact dans la table Person.Contact.
SELECT IndividualCustomer.Name, SOH.SalesOrderID
FROM (SELECT FirstName+ ' '+LastName as Name, I.CustomerID
FROM Sales.Individual I, Person.Contact C
WHERE I.ContactID = C.ContactID) IndividualCustomer
left outer join Sales.SalesOrderHeader SOH
ON IndividualCustomer.CustomerID = SOH.CustomerID
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerID
FOR XML AUTO
Voici le résultat partiel :
<IndividualCustomer Name="Jon Yang">
<SOH SalesOrderID="43793" />
<SOH SalesOrderID="51522" />
<SOH SalesOrderID="57418" />
</IndividualCustomer>
...
...
D. Renvoi de données binaires
La requête suivante renvoie la photographie d'un employé à partir de la table Employees. Photo est une colonne de type image de la table Employees. Par défaut, le mode AUTO renvoie vers les données binaires une référence, en l'occurrence une URL relative pointant vers la racine virtuelle de la base de données dans laquelle la requête est exécutée. L'attribut de clé EmployeeID doit être spécifié pour identifier l'image. Lors de l'extraction d'une référence d'image telle qu'elle apparaît dans cet exemple, la clé primaire de la table doit aussi être spécifiée dans la clause SELECT pour identifier une ligne de façon univoque.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO
Voici le résultat obtenu :
-- result
<Production.ProductPhoto
ProductPhotoID="70"
ThumbNailPhoto= "dbobject/Production.ProductPhoto[@ProductPhotoID='70']/@ThumbNailPhoto" />
La même requête est exécutée avec l'option BINARY BASE64. Elle renvoie les données binaires dans un format codé en base 64.
SELECT ProductPhotoID, ThumbNailPhoto
FROM Production.ProductPhoto
WHERE ProductPhotoID=70
FOR XML AUTO, BINARY BASE64
Voici le résultat obtenu :
-- result
<Production.ProductPhoto ProductPhotoID="70" ThumbNailPhoto="Base64 encoded photo" />
Par défaut, lorsque vous utilisez le mode AUTO pour extraire des données binaires, une référence à une URL relative pointant vers la racine virtuelle de la base de données dans laquelle la requête a été exécutée est renvoyée à la place des données binaires. Cela se produit si l'option BINARY BASE64 n'est pas spécifiée.
Lorsque le mode AUTO renvoie une référence URL aux données binaires des bases de données ne respectant pas la casse et dont un nom de table ou de colonne spécifié dans la requête ne correspond pas au nom de table ou de colonne défini dans la base de données, la requête s'exécute. Toutefois, la casse renvoyée dans la référence n'est pas cohérente. Exemple :
SELECT PRODUCTPHOTOID, THUMBNAILPHOTO
FROM Production.PRODUCTPHOTO
WHERE PRODUCTPHOTOID=70
FOR XML AUTO
Voici le résultat obtenu :
<Production.PRODUCTPHOTO
PRODUCTPHOTOID="70"
THUMBNAILPHOTO= "dbobject/Production.PRODUCTPHOTO[@ProductPhotoID='70']/@ThumbNailPhoto" />
Cela peut poser un problème, notamment lorsque des requêtes dbobject sont exécutées sur une base de données respectant la casse. Pour l'éviter, la casse du nom de table ou de colonne spécifié dans les requêtes doit correspondre à celle du nom de table ou de colonne défini dans la base de données.
E. Présentation du codage
Cet exemple montre les différents codages qui se produisent dans les résultats.
Créez cette table :
CREATE TABLE [Special Chars] (Col1 char(1) primary key, [Col#&2] varbinary(50))
Ajoutez les données suivantes à la table :
INSERT INTO [Special Chars] values ('&', 0x20)
INSERT INTO [Special Chars] values ('#', 0x20)
Cette requête renvoie les données de la table. Le mode FOR XML AUTO est spécifié. Les données binaires sont renvoyées sous forme de référence.
SELECT * FROM [Special Chars] FOR XML AUTO
Voici le résultat obtenu :
<Special_x0020_Chars
Col1="#"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='#']/@Col_x0023__x0026_2"
/>
<Special_x0020_Chars
Col1="&"
Col_x0023__x0026_2="dbobject/Special_x0020_Chars[@Col1='&']/@Col_x0023__x0026_2"
/>
Voici le processus pour coder des caractères spéciaux dans le résultat :
- Dans le résultat de la requête, les caractères spéciaux XML et URL qui se trouvent dans les noms d'éléments et d'attributs renvoyés sont codés avec la valeur hexadécimale du caractère Unicode correspondant. Dans le résultat précédent, le nom d'élément <
Special Chars> est renvoyé comme <Special_x0020_Chars>. Le nom d'attribut <Col#&2> est renvoyé comme <Col_x0023__x0026_2>. Les caractères spéciaux XML et URL sont codés. - Si les valeurs des éléments ou des attributs contiennent une des cinq entités de caractères XML standard (', "", <, > et &), ces caractères XML spéciaux sont toujours codés selon le codage de caractère XML. Dans le résultat précédent, la valeur & contenue dans la valeur de l'attribut <
Col1> est codée sous la forme &. Cependant, le caractère « # » reste sous la forme « # » parce qu'il s'agit d'un caractère XML valide et non d'un caractère XML spécial. - Si les valeurs des éléments ou des attributs contiennent un des caractères URL spéciaux ayant donc une signification particulière dans l'URL, ils sont encodés seulement dans la valeur URL DBOBJECT et uniquement lorsque le caractère spécial fait partie d'un nom de table ou de colonne. Dans le résultat, le caractère « # » qui fait partie du nom de table Col#&2 est codé sous la forme _x0023_ dans l'URL DBOJBECT.
Voir aussi
Référence
Construction de code XML à l'aide de FOR XML