Exemple : extraction d'informations sur les employés

Cet exemple récupère un ID d’employé et un nom d’employé pour chaque employé. Dans la base de données AdventureWorks2012 , l’employeeID peut être obtenu à partir de la colonne BusinessEntityID dans la table Employee. Les noms des employés peuvent être obtenus à partir de 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 les éléments suivants :

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

Étant donné qu’il existe deux niveaux dans la hiérarchie, vous devez écrire deux SELECT requêtes et appliquer 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 comme 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;  

Il s’agit de la deuxième requête. Il récupère les valeurs de l’élément <Name> . Il attribue la valeur Tag à l'élément <Name> et la valeur d'étiquette Parent à 1, 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 requise ORDER BY . Vous devez trier l’ensemble de lignes d’abord par BusinessEntityID, puis par nom pour que les valeurs NULL du nom apparaissent en premier. En exécutant la requête suivante sans la clause FOR XML, vous pouvez voir la table universelle générée.

Il s’agit de 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>

...

Le premier SELECT spécifie les noms des colonnes dans l’ensemble de lignes résultant. Ces noms forment deux groupes de colonnes. Le groupe qui a Tag une valeur 1 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 <>Namecomme élément et FNameLName comme attributs.

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

Tag 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

...

Voici comment les lignes de la table universelle sont traitées pour produire l’arborescence XML résultante :

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

La deuxième ligne a la Tag valeur 2. Par conséquent, le groupe de colonnes qui a la Tag valeur 2 dans le nom de colonne, Name!2!FName, Name!2!LNameest identifié. Ces noms de colonnes identifient Name comme nom d’élément. Un élément <Name> est créé avec les attributs FName et LName. Les valeurs de colonne correspondantes sont ensuite affectées à ces attributs. Cette ligne identifie 1 en tant que Parent. Ce sous-é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 de classer les lignes dans la table universelle afin que FOR XML EXPLICIT puisse traiter l’ensemble de lignes dans l’ordre et générer le code XML souhaité.

Voir aussi

Utiliser le mode EXPLICIT avec FOR XML