CODEWITHSUNDEEP
sqlxmloracle-databasexslt
jellyfizz
jellyfizz

Reputation: 70

How to produce XML using SQL query on Oracle

I have a de-normalised DB table on Oracle, with the following sample data:

 repdate | house | house_type | house_addr | person_name | gender | rent_amt
---------|-------|------------|------------|-------------|--------|----------
01/03/16 | hs01  | 4-bed      | 1 pine st  | Bruce       | M      | 500
01/03/16 | hs01  | 4-bed      | 1 pine st  | Alfred      | M      | 300
01/03/16 | hs01  | 4-bed      | 1 pine st  | Selina      | F      | 400
01/03/16 | hs03  | 1-bed      | 4 baker rd | Joan        | F      | 200
01/03/16 | hs02  | 3-bed      | 7 pod st   | Barry       | M      | 400
01/03/16 | hs02  | 3-bed      | 7 pod st   | Caitlin     | F      | 400

I would like to generate a normalized XML out of this table, which would ideally look like:

<?xml version="1.0" encoding="UTF-8"?>
<records repdate="01/03/16">
  <record house="hs01" house_type="4-bed" house_addr="1 pine st">
    <lineitems>
      <lineitem person_name="Bruce" gender="M" rent_amt="500" />
      <lineitem person_name="Alfred" gender="M" rent_amt="300" />
      <lineitem person_name="Selina" gender="F" rent_amt="400" />
    </lineitems>
  </record>
  <record house="hs02" house_type="3-bed" house_addr="7 pod st">
    <lineitems>
      <lineitem person_name="Barry" gender="M" rent_amt="400" />
      <lineitem person_name="Caitlin" gender="F" rent_amt="400" />
    </lineitems>
  </record>
  <record house="hs03" house_type="1-bed" house_addr="4 baker rd">
    <lineitems>
      <lineitem person_name="Joan" gender="F" rent_amt="200" />
    </lineitems>
  </record>
</records>

I was trying out the XMLElement and XMLAgg function, but got a bit stuck. I also did some research that I could produce a simple XML for this, and use an XSLT Transform to apply a stylesheet to get the result that I need.

Which one would be the better approach, and what would be the easiest way to transform the data?

Appreciate anyone who is able to help shed a little light on this. Thanks.

Upvotes: 1

Views: 54

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

I think it is the easiest way to achieve it.Query will be generated separated xml for distinct dates.

  select xmlelement("records", XMLATTRIBUTES(repdate as "repdate"), xmlagg(rec order by repdate, house))
    from (  select xmlelement(
                     "record"
                   , XMLATTRIBUTES(house as "house", house_type as "house_type", house_addr as "house_addr")
                   , xmlelement(
                       "lineitems"
                     , xmlagg(
                         xmlelement(
                           "lineitem"
                         , XMLATTRIBUTES(person_name as "person_name", gender as "gender", rent_amt as "rent_amt")))))
                     rec
                 , repdate
                 , house
              from your_table
          group by house
                 , house_type
                 , house_addr
                 , repdate)
group by repdate

Upvotes: 1

Related Questions