SQL FOR XML Help

Question

A simple question, but I'm new to XML formation in SQL 2005, but what would be best for the SQL XML statement to use the XML presented 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> 

Answer 1

Unverified:

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

Based on examples found here .