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:

  1. DECLARE @EXAMPLE TABLE
  2. (
  3. ID INT,
  4. Name VARCHAR(50)
  5. )
  6. INSERT INTO @EXAMPLE VALUES
  7. (1, 'C#1'),
  8. (2, 'C#2'),
  9. (3, 'C#3'),
  10. (4, 'C#4'),
  11. (5, 'C#5')
  12. SELECT * FROM @EXAMPLE FOR XML PATH('Node'), ROOT('Root')
OUTPUT
  1. <Root>
  2. <Node>
  3. <ID>1</ID>
  4. <Name>C#1</Name>
  5. </Node>
  6. <Node>
  7. <ID>2</ID>
  8. <Name>C#2</Name>
  9. </Node>
  10. <Node>
  11. <ID>3</ID>
  12. <Name>C#3</Name>
  13. </Node>
  14. <Node>
  15. <ID>4</ID>
  16. <Name>C#4</Name>
  17. </Node>
  18. <Node>
  19. <ID>5</ID>
  20. <Name>C#5</Name>
  21. </Node>
  22. </Root>
or you can convert your table data simply:

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