Retreive data from XML datatype in column format
I have a table in SQL which stores category data in XML format month and yearwise.
TABLE A
ID Int
Year Int
Month Int
Category XML
<root><category total="6" id="222" desc="General">
<country total="0" id="1" desc="Canada">
<area total="0" id="9" desc="Atlantic">
<state total="0" id="57" desc="New Brunswick" />
<state total="0" id="59" desc="Newfoundland" />
<state total="0" id="58" desc="Nova Scotia" />
<state total="0" id="63" desc="Prince Edward Island" />
</area>
</country>
<country total="6" id="2" desc="USA">
<area total="4" id="4" desc="Mid-West">
<state total="0" id="16" desc="Illinois" />
<state total="0" id="17" desc="Indiana" />
<state total="0" id="18" desc="Iowa" />
<state total="0" id="19" desc="Kansas" />
<state total="0" id="25" desc="Michigan" />
<state total="1" id="26" desc="Minnesota" />
<state total="0" id="28" desc="Missouri" />
<state total="0" id="30" desc="Nebraska" />
<state total="0" id="37" desc="North Dakota" />
<state total="1" id="38" desc="Ohio" />
<state total="0" id="44" desc="South Dakota" />
<state total="1" id="52" desc="Wisconsin" />
</area>
<area total="1" id="5" desc="South-West">
<state total="0" id="5" desc="Arizona" />
<state total="0" id="34" desc="New Mexico" />
<state total="0" id="39" desc="Oklahoma" />
<state total="1" id="46" desc="Texas" />
</area>
</country>
</category>
<category total="3" id="111" desc="Test">
<country total="0" id="1" desc="Canada">
<area total="0" id="9" desc="Atlantic">
<state total="0" id="57" desc="New Brunswick" />....
</area>
</country>
</category>
</root>
I need to get to display the data in following format.
Grouped by category with Area under each category and monthly totals
YEAR
Jan Feb Mar Apr May June July Aug Sep Oct...
Category
- Area
AdChoices