Edit

Example: Retrieve employee information

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This example retrieves an employee ID and employee name for each employee. In the AdventureWorks2022 database, the employeeID can be obtained from the BusinessEntityID column in the Employee table. Employee names can be obtained from the Person table. The BusinessEntityID column can be used to join the tables.

Assume that you want FOR XML EXPLICIT transformation to generate XML as shown in the following sample:

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

Because there are two levels in the hierarchy, you would write two SELECT queries and apply UNION ALL. This is the first query that retrieves values for the <Employee> element and its attributes. The query assigns 1 as Tag value for the <Employee> element and NULL as Parent, because it's the top-level element.

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;

This is the second query. It retrieves values for the <Name> element. It assigns 2 as Tag value for the <Name> element and 1 as Parent tag value identifying <Employee> as the 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;

You combine these queries with UNION ALL, apply FOR XML EXPLICIT, and specify the required ORDER BY clause. You must sort the rowset first by BusinessEntityID and then by name so that the NULL values in the name appear first. By executing the following query without the FOR XML clause, you can see the universal table generated.

This is the final query:

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;

This is the partial result:

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

The first SELECT specifies names for columns in the resulting rowset. These names form two column groups. The group that has Tag value 1 in the column name identifies Employee as an element and EmpID as the attribute. The other column group has Tag value 2 in the column and identifies <Name> as the element and FName and LName as the attributes.

The following table shows the partial rowset generated by the query:

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
... ... ... ... ...

This is how the rows in the universal table are processed to produce the resulting XML tree:

The first row identifies Tag value 1. Therefore, the column group that has the Tag value 1 is identified, Employee!1!EmpID. This column identifies Employee as the element name. An <Employee> element is then created that has EmpID attributes. Corresponding column values are assigned to these attributes.

The second row has the Tag value 2. Therefore, the column group that has the Tag value 2 in the column name, Name!2!FName, Name!2!LName, is identified. These column names identify Name as element name. A <Name> element is created that has FName and LName attributes. Corresponding column values are then assigned to these attributes. This row identifies 1 as Parent. This element child is added to the previous <Employee> element.

This process is repeated for rest of the rows in the rowset. Note the importance of ordering the rows in the universal table so that FOR XML EXPLICIT can process the rowset in order and generate the XML you want.

See also