How to produce XML using SQL query on Oracle

Question

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.

Arkadiusz Łukasiewicz · Accepted Answer · 2016-05-19 07:33:35Z

1

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

answered May 19, 2016 at 7:33

Arkadiusz Łukasiewicz

6,3761 gold badge13 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jellyfizz Over a year ago

Thanks, the sub-query from xmlelement("record"... was pretty much what I had. Adding the xmlelement("records"... on the top level produces 1 row instead of the 3 record rows that I had. Do you know if there is any limitation of how many characters can be contained in the resulting row? If there is more data, will having 1 row result oveflows the column?

Arkadiusz Łukasiewicz Over a year ago

Size. This page says 4gb for xmltype sotre in db .

Collectives™ on Stack Overflow

How to produce XML using SQL query on Oracle

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related