2 Replies Latest reply on Jan 16, 2013 10:21 PM by shah.parag79

    Retreive data from XML datatype in column format

    shah.parag79

      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