2 Replies Latest reply on Apr 10, 2013 6:03 PM by RjTan

    Help in xmlquery (newbie)

    RjTan
      Can somebody please show me how to accomplish this using both xmlquery and xmlelements/xmlagg.

      i have this table called table1 with values below
      StateName     City     City_Desc     Population
      ----------     ----     -------------     ----------
      California     SFO     San francisco     100
      California     SAC     Sacramento     200
      California     LA     Los Angeles     300
      Nevada          RN     Reno          100
      Nevada          LV     Las Vegas     400
      and i want to generate an xml output below
      <Country>
           <State>
                <StateName> California </StateName>
                     <values>
                          <City> SFO </City>
                          <City_Desc> San Francisco </City_Desc>
                          <Population> 100 </Population>
                        </values>
                     
                     <values>
                          <City> SAC </City>
                          <City_Desc> Sacramento </City_Desc>
                          <Population> 200 </Population>
                        </values>
      
                     <values>
                          <City> LA </City>
                          <City_Desc> Los Angeles </City_Desc>
                          <Population> 300 </Population>
                        </values>
      
           </State>
      
           <State>
                <StateName> Nevada </StateName>
                     <values>
                          <City> RN </City>
                          <City_Desc> reno </City_Desc>
                          <Population> 100 </Population>
                        </values>
                     
                     <values>
                          <City> LV </City>
                          <City_Desc> Las Vegas </City_Desc>
                          <Population> 400 </Population>
                        </values>
      
           </State>
      
      </Country>
        • 1. Re: Help in xmlquery (newbie)
          odie_63
          Not tested...

          Using SQL/XML functions :
          select xmlelement("Country",
                   xmlagg(
                     xmlelement("State",
                       xmlelement("StateName", statename)
                     , xmlagg(
                         xmlelement("values", 
                           xmlforest(
                             city as "City"
                           , city_desc as "City_Desc"
                           , population as "Population"
                           )
                         )
                       )
                     )
                   )
                 ) 
          from table1 
          group by statename ;
          Using XQuery :
          (replace "USER" with the actual owner of TABLE1)
          select xmlquery(
                   '<Country>
                    {
                      for $sn in distinct-values(fn:collection("oradb:/USER/TABLE1")/ROW/STATENAME)
                      return 
                        element State
                        {
                          element StateName {$sn}
                        , for $j in fn:collection("oradb:/USER/TABLE1")/ROW
                          where $j/STATENAME = $sn
                          return 
                            element values {
                              element City {$j/CITY/text()}
                            , element City_Desc {$j/CITY_DESC/text()}
                            , element Population {$j/POPULATION/text()}
                            } 
                        }
                    }
                    </Country>'
                   returning content
                 )
          from dual ;
          First one is better (only one access on the table).
          For the second one, as XQuery 1.0 doesn't support grouping operation, we need to do it in two steps :
          1- select distinct state name
          2- access the table again for each distinct state retrieved and aggregate the values
          • 2. Re: Help in xmlquery (newbie)
            RjTan
            Thank you. You are awesome and you make it sounds so simple :-)