Export (0) Print
Expand All

Example: Retrieving Employee Information

This example retrieves employee ID and employee names for each employee. In the AdventureWorks database, the employeeID can be obtained from the Employee table. Employee names can be obtained from the Contact table. The ContactID column can be used to join the tables.

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

<Employee EmpID="1" >
  <Name FName="Guy" LName="Gilbert" />
</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 is the top-level element.

SELECT 1    as Tag,
       NULL as Parent,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID

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,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID

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 EmployeeID 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,
       EmployeeID as [Employee!1!EmpID],
       NULL       as [Name!2!FName],
       NULL       as [Name!2!LName]
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
       1 as Parent,
       EmployeeID,
       FirstName, 
       LastName 
FROM   HumanResources.Employee E, Person.Contact C
WHERE  E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT

This is the partial result:

<Employee EmpID="1">
  <Name FName="Guy" LName="Gilbert" />
</Employee>
<Employee EmpID="2">
  <Name FName="Kevin" LName="Brown" />
</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                Guy           Gilbert
1    NULL     2                NULL          NULL
2     1       2                Kevin         Brown
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.

Community Additions

ADD
Show:
© 2014 Microsoft