Search code examples
sqlsql-serversql-server-2005t-sqlfor-xml

SQL FOR XML Help

Prob an easy question, but I am new to forming XML in SQL 2005, but What would be the best FOR XML SQL statement to use to form the XML seen below from a table that looks like this?

Column1     Column2   
------------------------
Baseball    Football   
Cricket     Polo       
Swim        Beach

Desired XML output:

<Category Name="Baseball">
  <Subcategory>Football</Subcategory>
</Category>
<Category Name="Cricket">
  <SubCategory>Polo</Subcategory>
</Category>
<Category Name="Swim">
  <SubCategory>Beach</Subcategory>
</Category>
Solution

  • Untested:

    SELECT t.column1 AS "@Name",
       t.column2 AS Subcategory
  FROM TABLE t
FOR XML PATH ('Category')

    Based on examples found here.