Exemples : utilisation du mode AUTO

Les exemples suivants illustrent l’utilisation du mode AUTO. La plupart de ces requêtes sont spécifiées par rapport aux documents XML d’instructions de fabrication de vélos stockés dans la colonne Instructions de la table ProductModel dans l’exemple de base de données AdventureWorks2012.

Exemple : récupération des informations sur les clients, les commandes et les détails de commande

Cette requête récupère les informations sur un client spécifique, incluant les détails des commandes passées par ce client.

USE AdventureWorks2012;  
GO  
SELECT Cust.CustomerID,   
       OrderHeader.CustomerID,  
       OrderHeader.SalesOrderID,   
       Detail.SalesOrderID, Detail.LineTotal, Detail.ProductID,   
       Product.Name,  
       Detail.OrderQty  
FROM Sales.Customer AS Cust  
INNER JOIN Sales.SalesOrderHeader AS OrderHeader   
    ON Cust.CustomerID = OrderHeader.CustomerID  
INNER JOIN Sales.SalesOrderDetail AS Detail  
    ON OrderHeader.SalesOrderID = Detail.SalesOrderID  
INNER JOIN Production.Product AS Product  
    ON Product.ProductID = Detail.ProductID  
WHERE Cust.CustomerID IN (29672, 29734)  
ORDER BY OrderHeader.CustomerID,  
         OrderHeader.SalesOrderID  
FOR XML AUTO;

Étant donné que la requête identifie, Cust, OrderHeader, Detailet Product les alias de table, les éléments correspondants sont générés par le AUTO mode. Là encore, l’ordre dans lequel les tables sont identifiées par les colonnes spécifiées dans la SELECT clause déterminent la hiérarchie de ces éléments.

Le résultat partiel est le suivant.

<Cust CustomerID="29672">

<OrderHeader CustomerID="29672" 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="29672" SalesOrderID="47660">

<Detail SalesOrderID="47660" LineTotal="469.794000" ProductID="765" OrderQty="1">

<Product Name="Road-650 Black, 58" />

</Detail>

</OrderHeader>

<OrderHeader CustomerID="29672" SalesOrderID="49857">

<Detail SalesOrderID="49857" LineTotal="44.994000" ProductID="852" OrderQty="1">

<Product Name="Women's Tights, S" />

</Detail>

</OrderHeader>

...

</Cust>

Exemple : spécification des fonctions GROUP BY et d’agrégation

La requête suivante retourne des ID de client individuels et le nombre de commandes demandées par le client.

USE AdventureWorks2012;  
GO  
SELECT C.CustomerID, COUNT(*) AS NoOfOrders  
FROM Sales.Customer AS C  
INNER JOIN Sales.SalesOrderHeader AS SOH  
On C.CustomerID = SOH.CustomerID  
GROUP BY C.CustomerID  
FOR XML AUTO;This is the partial result:

<I CustomerID="11000" NoOfOrders="3" />

<I CustomerID="11001" NoOfOrders="3" />

...

Exemple : spécification de colonnes calculées en mode AUTO

Cette requête retourne des noms de clients individuels concaténés et les informations de commande. Étant donné que la colonne calculée est affectée au niveau le plus interne rencontré à ce stade, l’élément <SOH> de cet exemple. Les noms de clients concaténés sont ajoutés en tant qu’attributs de l’élément <SOH> dans le résultat.

USE AdventureWorks2012;  
GO  
SELECT P.FirstName + ' ' + P.LastName AS Name,  
       SOH.SalesOrderID  
FROM Sales.Customer AS C  
INNER JOIN Sales.SalesOrderHeader AS SOH  
    ON  C.CustomerID = SOH.CustomerID  
INNER JOIN Person.Person AS P  
    ON P.BusinessEntityID = C.PersonID  
FOR XML AUTO;

Voici le résultat partiel :

<SOH Name="Jon Yang" SalesOrderID="43793" />  
<SOH Name="Eugene Huang" SalesOrderID="43767" />

Pour récupérer les éléments ayant l’attribut <IndividualCustomer>Name qui contient chaque information d’en-tête de commande client en tant que sous-élément, la requête est réécrite à l’aide d’une sous-sélection. La sélection interne crée une table temporaire IndividualCustomer avec la colonne calculée qui contient les noms des clients individuels. Cette table est ensuite jointe à la SalesOrderHeader table pour obtenir le résultat.

Notez que la Sales.Customer table stocke des informations client individuelles, y compris la PersonID valeur de ce client. Il PersonID est ensuite utilisé pour rechercher le nom du contact à partir de la table Person.Person.

SELECT IndividualCustomer.Name, SOH.SalesOrderID  
FROM (SELECT FirstName+ ' '+LastName AS Name, C.PersonID, C.CustomerID  
      FROM Sales.Customer AS C, Person.Person AS P  
      WHERE C.PersonID = P.BusinessEntityID) AS IndividualCustomer  
LEFT OUTER JOIN  Sales.SalesOrderHeader AS SOH  
   ON IndividualCustomer.CustomerID = SOH.CustomerID  
ORDER BY IndividualCustomer.CustomerID, SOH.CustomerIDFOR XML AUTO;

Voici le résultat partiel :

<IndividualCustomer Name="Jon Yang">

<SOH SalesOrderID="43793" />

<SOH SalesOrderID="51522" />

<SOH SalesOrderID="57418" />

</IndividualCustomer>

...

Exemple : renvoi de données binaires

Cette requête retourne une photo de produit à partir de la ProductPhoto table. ThumbNailPhoto est une varbinary(max) colonne dans la ProductPhoto table. Par défaut, AUTO le mode retourne aux données binaires une référence qui est une URL relative à la racine virtuelle de la base de données où la requête est exécutée. L’attribut ProductPhotoID clé doit être spécifié pour identifier l’image. Lors de la récupération d’une référence d’image comme illustré dans cet exemple, la clé primaire de la table doit également être spécifiée dans la SELECT clause pour identifier de manière unique une ligne.

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 . La requête retourne les données binaires au format codé en base64.

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 récupérer des données binaires, une référence à une URL relative à la racine virtuelle de la base de données où la requête a été exécutée est retournée au lieu des données binaires. Cela se produit si l’option BINARY BASE64 n’est pas spécifiée.

Lorsque le mode AUTO retourne une référence d’URL aux données binaires dans des bases de données insensibles à la casse, où un nom de table ou de colonne spécifié dans la requête ne correspond pas au nom de table ou de colonne de la base de données, la requête s’exécute. Toutefois, le cas retourné dans la référence ne sera pas cohérent. Par 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" />

Il peut s’agir d’un problème en particulier lorsque les requêtes dbobject sont exécutées sur une base de données sensible à la casse. Pour éviter cela, le cas du nom de table ou de colonne spécifié dans les requêtes doit correspondre au cas de la table ou du nom de colonne dans la base de données.

Exemple : Présentation de l’encodage

Cet exemple montre les différents encodages qui se produisent dans le résultat.

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), ('#', 0x20);

Cette requête retourne les données de la table. Le mode AUTO FOR XML est spécifié. Les données binaires sont retourné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"

/>

Il s’agit du processus d’encodage de caractères spéciaux dans le résultat :

Dans le résultat de la requête, les caractères XML et URL spéciaux dans les noms d’élément et d’attribut retournés sont encodés à l’aide de la valeur hexadécimale du caractère Unicode correspondant. Dans le résultat précédent, le nom <Special Chars> de l’élément est retourné en tant que .<Special_x0020_Chars> Le nom <Col#&2> de l’attribut est retourné en tant que .<Col_x0023__x0026_2> Les caractères spéciaux XML et URL sont encodés.
Si les valeurs des éléments ou attributs contiennent l’une des cinq entités de caractères XML standard (', « », <>et &), ces caractères XML spéciaux sont toujours encodés à l’aide de l’encodage de caractères XML. Dans le résultat précédent, la valeur & dans la valeur de l’attribut <Col1> est encodée en tant que &. Toutefois, le caractère # reste #, car 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 attributs contiennent des caractères d’URL spéciaux qui ont une signification particulière dans l’URL, ils sont encodés uniquement dans la valeur d’URL DBOBJECT et sont encodés uniquement lorsque le caractère spécial fait partie d’une table ou d’un nom de colonne. Dans le résultat, le caractère # qui fait partie du nom Col#&2 de la table est encodé en tant que _x0023_ in the DBOJBECT URL.

Voir aussi

Utiliser le mode AUTO avec FOR XML

Last updated on 2017-06-13

Partager via

Exemples : utilisation du mode AUTO

Exemple : récupération des informations sur les clients, les commandes et les détails de commande

Exemple : spécification des fonctions GROUP BY et d’agrégation

Exemple : spécification de colonnes calculées en mode AUTO

Exemple : renvoi de données binaires

Exemple : Présentation de l’encodage

Voir aussi

Ressources supplémentaires