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.

For XML Explicit in SQL Server

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.

24 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!