Get the Table data as Xml in SQL SERVER

In This post we are going to see how to get the data from the table as XML in SQL SERVER.

Data are stored as XML in SQL SERVER internally, but when we view in UI, it looks like Grid or Flat file in Management studio

 The output of the explicit mode contains all the information of the resulting XML tree. The FOR XML clause in EXPLICIT mode associates with every element, a tag number of the current element and tag number for parent element. This tag numbers are stored in separate columns, named Tag and Parent Respectively.

SELECT      1           AS Tag,

            NULL        AS Parent,

            id          AS [Employees!1!id],

            department AS [Employees!1!dept],

            name        AS[Employees!1]

FROM  employee

FOR XML EXPLICIT

Output:

            <Employees id="2" dept="Tech">DF</Employees>

<Employees id="3" dept="HR">Siv</Employees>

<Employees id="4" dept="BA">SS</Employees>

           

From this post you can learn how to get the data from table as XML in SQL SERVER