SQL SERVER Convert Table Data to XML Format using XML Path

Here I will explain how to convert table data to xml format in sql server or get table data in xml format in sql server. We can convert sql server table data in xml format by using XML PATH.

We can convert table data in xml format in different ways:

Using XML Path Statement

Here We use XML Path statement to convert table data to xml as shown below:

DECLARE @EXAMPLE TABLE
(
ID INT,
Name VARCHAR(50)
)
INSERT INTO @EXAMPLE VALUES
(1, 'C#1'),
(2, 'C#2'),
(3, 'C#3'),
(4, 'C#4'),
(5, 'C#5')
SELECT * FROM @EXAMPLE FOR XML PATH('Node'), ROOT('Root')

OUTPUT

<Root>
<Node>
<ID>1</ID>
<Name>C#1</Name>
</Node>
<Node>
<ID>2</ID>
<Name>C#2</Name>
</Node>
<Node>
<ID>3</ID>
<Name>C#3</Name>
</Node>
<Node>
<ID>4</ID>
<Name>C#4</Name>
</Node>
<Node>
<ID>5</ID>
<Name>C#5</Name>
</Node>
</Root>

or you can convert your table data simply:

Select * from YourTable FOR XML PATH('Node'), ROOT('Root').