Exemple : Récupérer des informations sur les employés

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

Cet exemple extrait un ID et un nom pour chaque employé. Dans la base de données AdventureWorks2022 , les ID des employés se trouvent dans la colonne BusinessEntityID de la table Employee. Les noms des employés figurent dans la table Person. La colonne BusinessEntityID peut être utilisée pour joindre les tables.

Supposons que vous souhaitez que la transformation FOR XML EXPLICIT génère du code XML comme indiqué dans l’exemple suivant :

<Employee EmpID="1" >
  <Name FName="Ken" LName="Sánchez" />
</Employee>
...

Étant donné que la hiérarchie comprend deux niveaux, vous écrivez deux requêtes SELECT et appliquez UNION ALL. Il s’agit de la première requête qui récupère les valeurs de l’élément <Employee> et de ses attributs. La requête affecte 1 comme Tag valeur pour l’élément <Employee> et NULL en tant que Parent, car il s’agit de l’élément de niveau supérieur.

SELECT 1    as Tag,
       NULL as Parent,
       E.BusinessEntityID AS [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID;

Voici la seconde requête. Il récupère les valeurs de l’élément <Name> . Il affecte 2 comme Tag valeur pour l’élément <Name> et 1 comme Parent valeur d’étiquette identifiant <Employee> comme parent.

SELECT 2 as Tag,
       1 as Parent,
       E.BusinessEntityID,
       FirstName,
       LastName
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID;

Vous combinez ces requêtes avec UNION ALL, appliquez FOR XML EXPLICITet spécifiez la clause ORDER BY requise. Vous devez trier l'ensemble de lignes par BusinessEntityID puis par nom afin que les valeurs NULL du nom apparaissent en premier. En exécutant la requête suivante sans la clause FOR XML, vous pouvez visualiser la table universelle générée.

Voici la requête finale :

SELECT 1    as Tag,
       NULL as Parent,
       E.BusinessEntityID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       E.BusinessEntityID,
       FirstName,
       LastName
FROM   HumanResources.Employee AS E
INNER JOIN Person.Person AS P
ON  E.BusinessEntityID = P.BusinessEntityID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT;

Voici le résultat partiel :

<Employee EmpID="1">
  <Name FName="Ken" LName="Sánchez" />
</Employee>
<Employee EmpID="2">
  <Name FName="Terri" LName="Duffy" />
</Employee>
...

La première instruction SELECT spécifie les noms des colonnes dans l'ensemble de lignes obtenu. Ces noms forment deux groupes de colonnes. Le groupe qui possède la valeur Tag1 dans le nom de colonne identifie Employee en tant qu'élément et EmpID en tant qu'attribut. L’autre groupe de colonnes a Tag une valeur 2 dans la colonne et identifie <Name> comme élément et FNameLName comme attributs.

Le tableau suivant montre l'ensemble de lignes partiel généré par la requête :

Balise	Parent	Employee!1!EmpID	Name!2!FName	Name!2!LName
1	NULL	1	NULL	NULL
2	1	1	Ken	Sánchez
1	NULL	2	NULL	NULL
2	1	2	Terri	Duffy
1	NULL	3	NULL	NULL
2	1	3	Roberto	Tamburello
...	...	...	...	...

Les lignes de la table universelle sont traitées comme suit pour générer l'arborescence XML obtenue :

La première ligne identifie la valeur Tag1. Par conséquent, le groupe de colonnes qui a la valeur Tag1 est identifié, Employee!1!EmpID. Cette colonne identifie Employee en tant que nom d'élément. Un <Employee> élément est ensuite créé avec EmpID des attributs. Les valeurs de colonnes correspondantes sont affectées à ces attributs.

La deuxième ligne a la valeur Tag2. Par conséquent, le groupe de colonnes qui a la valeur Tag2 dans le nom de colonne, Name!2!FName, Name!2!LName, est identifié. Ces noms de colonnes identifient Name comme nom d'élément. Un <Name> élément est créé avec FName des attributs et LName des attributs. Les valeurs de colonnes correspondantes sont ensuite affectées à ces attributs. Cette ligne identifie 1 comme Parent. Cet enfant d’élément est ajouté à l’élément précédent <Employee> .

Ce processus est répété pour le reste des lignes de l'ensemble de lignes. Notez l'importance du tri des lignes dans la table universelle pour que FOR XML EXPLICIT puisse traiter l'ensemble de lignes dans l'ordre et générer le document XML souhaité.

Voir aussi

• Utiliser le mode EXPLICIT avec FOR XML