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 ALLSELECT 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<20FOR XML EXPLICIT