Loading

SQL SERVER Convert Table Data to XML Format using XML Path

Digvijay Raghav

Comments

Join the conversation! Your thoughts help the community grow.

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').