Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare nowThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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 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:
<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.
Events
Get certified in Microsoft Fabric—for free!
19 Nov, 11 pm - 10 Dec, 11 pm
For a limited time, the Microsoft Fabric Community team is offering free DP-600 exam vouchers.
Prepare now