---
title: "Example: Retrieving Employee Information | Microsoft Docs"
ms.custom: ""
ms.date: "06/13/2017"
ms.prod: "sql-server-2014"
ms.reviewer: ""
ms.technology: xml
ms.topic: conceptual
helpviewer_keywords:
- "EXPLICIT mode"
ms.assetid: 63cd6569-2600-485b-92b4-1f6ba09db219
author: MightyPen
ms.author: genemi
manager: craigg
---
# Example: Retrieving Employee Information
This example retrieves an employee ID and employee name for each employee. In the [!INCLUDE[ssSampleDBobject](../../includes/sssampledbobject-md.md)] 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:
```
...
```
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 is 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 AL`L, 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:
``
``
``
``
``
``
`...`
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
[Use EXPLICIT Mode with FOR XML](use-explicit-mode-with-for-xml.md)