Utiliser le mode AUTO avec FOR XML

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Comme décrit dans FOR XML (SQL Server), le mode AUTO retourne les résultats de la requête en tant qu’éléments XML imbriqués. Cela ne permet pas de contrôler beaucoup la forme du code XML généré à partir d’un résultat de requête. Les requêtes au mode AUTO sont utiles pour générer des hiérarchies simples. Toutefois, Utiliser le mode EXPLICIT avec FOR XML et Utiliser le mode PATH avec FOR XML fournissent davantage de contrôle et de souplesse pour déterminer la forme du XML à partir d’un résultat de requête.

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 <Cust> élément est créé et CustomerID est ajouté en tant qu’attribut.

  • Ensuite, trois colonnes, OrderHeader.CustomerID, OrderHeader.SaleOrderID et OrderHeader.Status, référencent la table OrderHeader. Par conséquent, un <OrderHeader> élément 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éé. Au lieu de cela, l’attribut CustomerType est ajouté à l’élément <Cust> qui a été créé précédemment.

  • 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, le premier <OrderHeader> élément est créé, puis l’élément <Cust> enfant est ajouté à celui-ci.

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é comme la clé primaire de la table, toutes les valeurs de colonne (CustomerID, 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 code XML.

Lors de la comparaison de ces valeurs de colonnes, si l’une des colonnes à comparer est de type text, ntext, imageou 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 d’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 (n)varchar(max) et varbinary(max) sont comparées.

Lorsqu’une colonne de la clause SELECT ne peut pas être associée à l’une des tables identifiées dans la clause FROM, comme dans le cas d’une colonne d’agrégation ou d’une colonne calculée, la colonne est ajoutée dans le document XML dans le niveau d’imbrication le plus profond en place lorsqu’elle est rencontrée 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.

Étapes suivantes

Les articles suivants fournissent plus d’informations sur le mode AUTO :

Voir aussi