EasyCFM.COM ColdFusion Forums / Coding Help! / Retreive data from XML datatype in column format

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Retreive data from XML datatype in column format

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

paragshah79
01-16-2013 @ 4:30 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Member
Posts:
Joined: Jan 2013

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...
Category
  - Area                                                            



Website Designed and Developed by Pablo Varando.