Creating XML Structure from Database Table using SQL Server

Data and Databases
1

I’m working with SQL Server and need help generating XML from a database table. I have a simple table with two columns and want to transform the data into a specific XML format.

My table structure:

Product     Type
-----------------
Laptop      Gaming
Phone       Mobile
Tablet      Portable

Expected XML result:

<Item Name="Laptop">
  <Category>Gaming</Category>
</Item>
<Item Name="Phone">
  <Category>Mobile</Category>
</Item>
<Item Name="Tablet">
  <Category>Portable</Category>
</Item>

I’m pretty new to using FOR XML in SQL queries and not sure which approach would work best here. Can someone show me the right SQL statement to achieve this XML output? Any guidance would be really helpful.

2

hey, you could use this query: SELECT Product as '@Name', Type as 'Category' FROM YourTable FOR XML PATH('Item'), ROOT('Items'). the @ sign makes an attribute, and PATH does the XML thing. super easy for simple XML stuff!

3

interesting! maya’s solution looks solid, but doesn’t that ROOT(‘Items’) wrapper add extra nesting? also, do you need to handle special characters in product names or categories? have you tested the query yet?

4

You’re on the right track with FOR XML PATH, but drop the ROOT clause. Try this instead: SELECT Product as '@Name', Type as 'Category' FROM YourTable FOR XML PATH('Item'). The ROOT wrapper adds an extra parent element you don’t want. I ran into this exact problem building XML reports for our inventory system. PATH gives you clean control over the structure, and the @ symbol maps your Product column to the Name attribute like you need.