How to convert Table data into XML in SQL Server

Last Updated: 06th April 2024

There are three simple methods in SQL Server that you can use to convert table data into XML format using a simple Query.

Let us assume, I have a table named dbo.birds, which has a list of birds with id, name, the type of bird and scientific names each bird.

I want convert the table data into an XML format. I can convert all the data or specific data.

1) Using PATH mode Clause with FOR XML

In the first example, I am using the XML PATH mode as a clause to a SELECT query. This simple query convert the table data into XML format.

SELECT *FROM dbo.Birds FOR XML PATH

Run the query. It will show you a link in the "result" window. Clicking the link will open a "new query editor window" with the table data in XML format.

Output


<row>
  <ID>1</ID>
  <BirdName>Eurasian Collared-Dove</BirdName>
  <TypeOfBird>Dove</TypeOfBird>
  <ScientificName>Streptopelia</ScientificName>
</row>
<row>
  <ID>2</ID>
  <BirdName>Bald Eagle</BirdName>
  <TypeOfBird>Hawk</TypeOfBird>
  <ScientificName>Haliaeetus Leucocephalus</ScientificName>
</row>

For each row, a <row> tag is added and it has converted the table "column names" as elements. The values are wrapped inside an element with opening and closing tag.

The <row> tag is default. However, you can specify a name (of your choice) to the "row element". For example,

 SELECT *FROM dbo.Birds FOR XML PATH('Birds')

Output

<Birds>
  <ID>1</ID>
  <BirdName>Eurasian Collared-Dove</BirdName>
  <TypeOfBird>Dove</TypeOfBird>
  <ScientificName>Streptopelia</ScientificName>
</Birds>
<Birds>
  <ID>2</ID>
  <BirdName>Bald Eagle</BirdName>
  <TypeOfBird>Hawk</TypeOfBird>
  <ScientificName>Haliaeetus Leucocephalus</ScientificName>
</Birds>

Now, each rowset has the <bird> tag.

You can also add a root element to the rowset, that is, "wrap all the rows" inside a root element.

For example, I’ll now extract only birds of type Hawk and add a "root element" to the rowset by specifying the ROOT option.

SELECT *FROM dbo.Birds WHERE TypeOfBird = 'Hawk' FOR XML PATH('Hawk'), ROOT

Output

<Bird>
  <Hawk>
    <ID>2</ID>
    <BirdName>Bald Eagle</BirdName>
    <TypeOfBird>Hawk</TypeOfBird>
    <ScientificName>Haliaeetus Leucocephalus</ScientificName>
  </Hawk>
  <Hawk>
    <ID>3</ID>
    <BirdName>Cooper's Hawk</BirdName>
    <TypeOfBird>Hawk</TypeOfBird>
    <ScientificName>Accipiter Cooperii</ScientificName>
  </Hawk>
</Bird>

2) Using RAW mode Clause with FOR XML

Unlike the PATH mode that converts table columns into elements (or tags), the ROW mode "converts each row into an element" and columns as v. For example,

SELECT *FROM dbo.Birds WHERE TypeOfBird = 'Dove' FOR XML RAW

Output

<row ID="1" BirdName="Eurasian Collared-Dove" TypeOfBird="Dove" ScientificName="Streptopelia" />
<row ID="5" BirdName="Mourning Dove" TypeOfBird="Dove" ScientificName="Zenaida Macroura" />
<row ID="6" BirdName="Rock Pigeon" TypeOfBird="Dove" ScientificName="Columba Livia" />

You can provide ROW mode with a name. For example,

SELECT *FROM dbo.Birds WHERE TypeOfBird = 'Dove' FOR XML RAW ('Bird')

Here again, you can add a root element by specifying the ROOT option.

SELECT *FROM dbo.Birds WHERE TypeOfBird = 'Dove' FOR XML RAW ('Dove'), ROOT ('Bird')

<Bird>
  <Dove ID="1" BirdName="Eurasian Collared-Dove" TypeOfBird="Dove" ScientificName="Streptopelia" />
  <Dove ID="5" BirdName="Mourning Dove" TypeOfBird="Dove" ScientificName="Zenaida Macroura" />
  <Dove ID="6" BirdName="Rock Pigeon" TypeOfBird="Dove" ScientificName="Columba Livia" />
</Bird>

3) Using AUTO mode Clause with FOR XML

If you want to create hierarchies in XML format, then you should use the AUTO mode. The auto mode returns an output as Nested XML elements.

Now, for this example I am using two tables, which has a "relationship" (parent and child). The table names are "Employee" and "EmployeeDetails". See the table structures.

I want to create an XML with nested elements (or details) for each employee.

SELECT emp.EmpName, EmpDet.Email, EmpDet.Mobile
    FROM dbo.Employee Emp, dbo.EmployeeDetails EmpDet
    WHERE Emp.EmpID = EmpDet.EmpID
    FOR XML AUTO

Output

<Emp EmpName="Benjamin">
  <EmpDet Email="chinxyz@emp.com" Mobile="1234567879" />
</Emp>
<Emp EmpName="Shanhey">
  <EmpDet Email="mike230@emample.com" Mobile="2152313213" />
</Emp>

sql server - convert table data into xml format using a query

← PreviousNext →