CODEWITHSUNDEEP
sqlxmloracle-database
proximityFlying
proximityFlying

Reputation: 183

SQL query to produce XML result

I am trying to produce the following XML when querying a database. The database contains two tables COUNTRY and STATES -

 <Country>
   <CountryInfo>
       <name>USA</name>
       <districts>50</districts>
       <state>
           <stateName>New York</stateName>
           <statePop>8,244,910</statePop>
       </state>
       <state>
           <stateName>Chicago</stateName>
           <statePop>State Population: 2,707,120</statePop>
       </state>
 </CountryInfo>
 <CountryInfo>
       <name>Germany</name>
       <districts>16</districts>
       <state>
           <stateName>Berlin</stateName>
           <statePop>3,469,910</statePop>
       </state>
       <state>
           <stateName>Brandenburg</stateName>
           <statePop>2,500,000</statePop>
       </state> 
   </CountryInfo>
 </Country>

Here is an attempt -

 select ctry.NAME, ctry.DISTRICTS, st.ST_NAME, st.ST_POPULATION
 from COUNTRY ctry inner join STATES st on (ctry.NAME=st.COUNTRY);

The result is a flat file.

 <CountryCollection>
   <COUNTRY>
     <NAME>USA</NAME>
     <DISTRICTS>50</DISTRICTS>
     <ST_NAME>New York</ST_NAME>
     <ST_POPULATION>8,244,910</ST_POPULATION>
   </COUNTRY>
   <COUNTRY>
     <NAME>USA</NAME>
     <DISTRICTS>50</DISTRICTS>
     <ST_NAME>CHICAGO</ST_NAME>
     <ST_POPULATION> 2,707,120</ST_POPULATION>
   </COUNTRY>
   <COUNTRY>
     <NAME>GERMANY</NAME>
     <DISTRICTS>16</DISTRICTS>
     <ST_NAME>Berlin</ST_NAME>
     <ST_POPULATION>3,469,910</ST_POPULATION>
   </COUNTRY>
   <COUNTRY>
     <NAME>GERMANY</NAME>
     <DISTRICTS>50</DISTRICTS>
     <ST_NAME>Brandenburg</ST_NAME>
     <ST_POPULATION>2,500,000</ST_POPULATION>
   </COUNTRY>
 </CountryCollection>

How could I group them to get the desired result?

Upvotes: 0

Views: 144

Answers (1)

Ftaveras
Ftaveras

Reputation: 719

Since you are using a oracle RDBMS try this.

SELECT XMLElement("Country" 
  , XMLAgg(
    XMLElement("CountryInfo"
      ,XMLFOREST(CTRY.NAME "name", ctry.DISTRICTS "districts") 
      , xmlAgg(
        XMLElement("state",XMLFOREST(st.ST_NAME "stateName", st.ST_POPULATION  "statePop") 
        ) 
      ) 
    ) 
  ) 
)
FROM COUNTRY ctry
INNER JOIN STATES st
ON (ctry.NAME=st.COUNTRY)
GROUP BY CTRY.NAME ,ctry.DISTRICTS

Edited to apply correction by @A.B.Cade.

Upvotes: 1

Related Questions