Beispiel: Abrufen von Informationen zu Mitarbeitern

In diesem Beispiel wird eine Mitarbeiter-ID und ein Mitarbeitername für jeden Mitarbeiter abgerufen. In der AdventureWorks2012-Datenbank kann die EmployeeID aus der Spalte "BusinessEntityID" in der Tabelle "Mitarbeiter" abgerufen werden. Mitarbeiternamen können aus der Tabelle "Person" abgerufen werden. Die Spalte "BusinessEntityID" kann verwendet werden, um die Tabellen zu verknüpfen.

Angenommen, Sie möchten, dass DIE FOR XML EXPLICIT-Transformation XML generiert, wie in den folgenden Beispielen dargestellt:

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

Da es zwei Ebenen in der Hierarchie gibt, würden Sie zwei SELECT Abfragen schreiben und UNION ALL anwenden. Dies ist die erste Abfrage, die Werte für das <Employee> Element und die zugehörigen Attribute abruft. Die Abfrage weist 1 als Tag Wert für das <Employee> Element und NULL als Parent zu, da es sich um das Element der obersten Ebene handelt.

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;  

Dies ist die zweite Abfrage. Es ruft Werte für das <Name> Element ab. Es weist 2 als Tag Wert für das <Name>-Element zu und 1 als Parent-Tagwert, während <Employee> als übergeordnetes Element identifiziert wird.

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;  

Sie kombinieren diese Abfragen mit UNION ALL, wenden FOR XML EXPLICIT an und geben die erforderliche ORDER BY Klausel an. Sie müssen das Rowset zuerst nach BusinessEntityID und dann nach Namen sortieren, damit die NULL-Werte im Namen zuerst angezeigt werden. Wenn Sie die folgende Abfrage ohne die FOR XML-Klausel ausführen, wird die universelle Tabelle generiert.

Dies ist die endgültige Abfrage:

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;  

Dies ist das Teilergebnis:

<Employee EmpID="1">

<Name FName="Ken" LName="S??nchez" />

</Employee>

<Employee EmpID="2">

<Name FName="Terri" LName="Duffy" />

</Employee>

...

Der erste SELECT gibt Namen für Spalten im resultierenden Rowset an. Diese Namen bilden zwei Spaltengruppen. Die Gruppe, die Tag Wert 1 im Spaltennamen hat, identifiziert Employee als Element und EmpID als Attribut. Die andere Spaltengruppe weist Tag einen Wert 2 in der Spalte auf und identifiziert <Name> als das Element und FName und LName als Attribute.

Die folgende Tabelle zeigt das partielle Rowset, das von der Abfrage generiert wird:

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

...

So werden die Zeilen in der universellen Tabelle verarbeitet, um die resultierende XML-Struktur zu erzeugen:

Die erste Zeile identifiziert Tag den Wert 1. Daher wird die Spaltengruppe mit dem Tag Wert 1 identifiziert. Employee!1!EmpID Diese Spalte identifiziert Employee den Elementnamen. Anschließend wird ein <Employee> Element mit Attributen erstellt EmpID . Diesen Attributen werden entsprechende Spaltenwerte zugewiesen.

Die zweite Zeile weist den Tag Wert 2auf. Daher wird die Spaltengruppe, die den Tag Wert 2 im Spaltennamen , Name!2!FName, Name!2!LNamehat, identifiziert. Diese Spaltennamen identifizieren Name als Elementnamen. Ein <Name> Element wird erstellt, das die Attribute FName und LName hat. Die entsprechenden Spaltenwerte werden diesen Attributen zugewiesen. Diese Zeile identifiziert 1 als Parent. Dieses untergeordnete Element wird dem vorherigen <Employee> Element hinzugefügt.

Dieser Vorgang wird für die restlichen Zeilen im Rowset wiederholt. Beachten Sie die Wichtigkeit der Sortierung der Zeilen in der universellen Tabelle, damit FOR XML EXPLICIT das Rowset in der Reihenfolge verarbeiten und den gewünschten XML-Code generieren kann.

Siehe auch

Verwenden des EXPLIZITEN Modus mit FOR XML