For XML Explicit in SQL Server

Hello everyone,

In this article, I will talk about the use of For XML Explicit in SQL Server.

In SQL Server, For XML Explicit statement is used to output XML. The For XML Explicit statement is used with Tag and Parent statements. You might be a little confused when used for the first time, but don’t give up right away. When you start to understand how it is used, you will find that it really works. Let’s examine the example usage using the Northwind database.

SELECT 1 AS Tag,
       NULL AS Parent,
       e.EmployeeID AS [Employee!1!EmployeeId],
       e.LastName AS [Employee!1!LastName],
       NULL AS [EmployeeTerritories!2!TerritoryID]
FROM dbo.Employees e
UNION ALL
SELECT 2 AS Tag,
       1 AS Parent,
       et.EmployeeID AS [Employee!1!EmployeeId],
       NULL AS [Employee!1!LastName],
       et.TerritoryID AS [EmployeeTerritories!2!TerritoryID]
FROM dbo.EmployeeTerritories et
ORDER BY [Employee!1!EmployeeId],
         Parent
FOR XML EXPLICIT;

When you run the above query, you will see a result similar to the one below.

Here you see the structures like Employee!1!EmployeeId. Numbers such as 1 and 2 in these structures you have seen correspond to the numbers opposite the Tag and Parent expressions. Also, in the expression Employee!1!EmployeeId, the expression EmployeeId must be written the same as the table expression in the database.

In the first select query, e.EmployeeID corresponds to 1 -> Tag in AS [Employee!1!EmployeeId] structure.

In the second select query, et.EmployeeID corresponds to 1-> Parent in AS [Employee!1!EmployeeId] structure. 2 corresponds to Tag.

If you do a little digging, you will understand the structure better.

Good luck to everyone in business and life.