Create XML Data From SQL Server Using For XML Explicit

There are different ways to get data in XML format from SQL Server like:

• For XML Auto
• For XML RAW
• For XML Explicit

But in the first and second method we can't customize the format of XML. if we need customize XML then we need to use "For XML Explicit Method" With the Help of "For XML Explicit" we can create XML in the required format.

'Tag' and 'Parent' Column are used to determine the hierarchy of xml.
Now this code will generate a parent node name root and Session as Child of Root.

[Session!2!ID] this will create an attribute of Session Node Named ID.
and [Session!2!Notes!element] this will create a Child element of Session Named Notes.

Sample Code:

SELECT 1 AS Tag,

NULL AS Parent,

NULL AS [Session!2!ID],

NULL AS [Session!2!Notes!element],

0 AS [root!1!Customer!hide]

UNION ALL

SELECT 2 AS Tag,

1 AS Parent,

Session.SessionID AS [Session!2!ID],

Session.Notes AS [Session!2!Notes!element],

1 AS [root!1!Customer!hide]

FROM tbl_Session AS Session WHERE Session.SessionID<20

FOR XML EXPLICIT