This discussion is archived
2 Replies Latest reply: Dec 17, 2012 4:40 AM by 884492 RSS

Generating XML from Oracle tables

884492 Newbie
Currently Being Moderated
I am using Oracle 11g R1.

I have two tables Header and Line:

CREATE TABLE HEADER
(
EXPENSE_INFO      VARCHAR2(2000),
ERROR_CODE NUMBER,
REASON_OF_FAILURE VARCHAR2(4000)
)
/


CREATE TABLE LINE
(
EXPENSE_INFO VARCHAR2(2000),
EXPENSE_DATE     DATE,
EXPENSE_CODE     VARCHAR2(10),
EXPENSE_REF     VARCHAR2(2000),
GROSS_AMOUNT     NUMBER,
TAX_AMOUNT      NUMBER,
ERROR_CODE NUMBER,
REASON_OF_FAILURE VARCHAR2(4000)
)
/

These tables are related through EXPENSE_INFO column.

I populate the tables with the following data:

insert into HEADER (EXPENSE_INFO, ERROR_CODE, REASON_OF_FAILURE)
values ('A', 0, 'SUCCESS');

insert into LINE (EXPENSE_INFO, EXPENSE_DATE, EXPENSE_CODE, EXPENSE_REF, GROSS_AMOUNT, TAX_AMOUNT, ERROR_CODE, REASON_OF_FAILURE)
values ('A', to_date('14-12-2012', 'dd-mm-yyyy'), 'ABC', 'HOTEL', 100, 500, 0, 'SUCCESS');

insert into LINE (EXPENSE_INFO, EXPENSE_DATE, EXPENSE_CODE, EXPENSE_REF, GROSS_AMOUNT, TAX_AMOUNT, ERROR_CODE, REASON_OF_FAILURE)
values ('A', to_date('14-12-2012', 'dd-mm-yyyy'), 'DEF', 'MOVIE', 10, 50, 1, 'Expense Code does not exist.');

insert into HEADER (EXPENSE_INFO, ERROR_CODE, REASON_OF_FAILURE)
values ('B', 1, 'Emp Id does not exist.');

insert into LINE (EXPENSE_INFO, EXPENSE_DATE, EXPENSE_CODE, EXPENSE_REF, GROSS_AMOUNT, TAX_AMOUNT, ERROR_CODE, REASON_OF_FAILURE)
values ('B', to_date('14-12-2012', 'dd-mm-yyyy'), 'XYZ', 'MILEAGE', 200, 300, 0, 'SUCCESS');



I want to generate the following XML from these tables:


<EXPENSES>
          <EXPENSE_DETAILS>
               <EXPENSE_INFO>A</EXPENSE_INFO>
               <ERROR_CODE>0</ERROR_CODE>
               <REASON_OF_FAILURE>SUCCESS</REASON_OF_FAILURE>
               <EXPENSE_LINES>
                    <EXPENSE_LINE_REC>
                         <EXPENSE_INFO>A</EXPENSE_INFO>
                         <EXPENSE_DATE>14-12-2012 00:00:00</EXPENSE_DATE>
                         <EXPENSE_CODE>ABC</EXPENSE_CODE>
                         <EXPENSE_REF>HOTEL</EXPENSE_REF>
                         <GROSS_AMOUNT>100</GROSS_AMOUNT>
                         <TAX_AMOUNT>500</TAX_AMOUNT>
                         <ERROR_CODE>0</ERROR_CODE>
                         <REASON_OF_FAILURE>SUCCESS</REASON_OF_FAILURE>
                    </EXPENSE_LINE_REC>
                    <EXPENSE_LINE_REC>
                         <EXPENSE_INFO>A</EXPENSE_INFO>
                         <EXPENSE_DATE>14-12-2012 00:00:00</EXPENSE_DATE>
                         <EXPENSE_CODE>DEF</EXPENSE_CODE>
                         <EXPENSE_REF>MOVIE</EXPENSE_REF>
                         <GROSS_AMOUNT>10</GROSS_AMOUNT>
                         <TAX_AMOUNT>50</TAX_AMOUNT>
                         <ERROR_CODE>1</ERROR_CODE>
                         <REASON_OF_FAILURE>Expense Code does not exist.</REASON_OF_FAILURE>
                    </EXPENSE_LINE_REC>
               </EXPENSE_LINES>
          </EXPENSE_DETAILS>
          <EXPENSE_DETAILS>
               <EXPENSE_INFO>B</EXPENSE_INFO>
               <ERROR_CODE>1</ERROR_CODE>
               <REASON_OF_FAILURE>Employee Id does not exist.</REASON_OF_FAILURE>
               <EXPENSE_LINES>
                    <EXPENSE_LINE_REC>
                         <EXPENSE_INFO>B</EXPENSE_INFO>
                         <EXPENSE_DATE>14-12-2012 00:00:00</EXPENSE_DATE>
                         <EXPENSE_CODE>XYZ</EXPENSE_CODE>
                         <EXPENSE_REF>MILEAGE</EXPENSE_REF>
                         <GROSS_AMOUNT>200</GROSS_AMOUNT>
                         <TAX_AMOUNT>300</TAX_AMOUNT>
                         <ERROR_CODE>0</ERROR_CODE>
                         <REASON_OF_FAILURE>SUCCESS</REASON_OF_FAILURE>
                    </EXPENSE_LINE_REC>
               </EXPENSE_LINES>
          </EXPENSE_DETAILS>
</EXPENSES>


I am not able to generate this XML. Kindly help me out.

Regards,
Sudhanshu
  • 1. Re: Generating XML from Oracle tables
    Stew Ashton Expert
    Currently Being Moderated
    I like to work from the innermost level and then "wrap around" with outer level tags.

    You want to list all the columns in each LINE row. XMLFOREST is good for this:
    select xmlforest(
      expense_info, expense_date, expense_code, expense_ref, 
      gross_amount, tax_amount, error_code, reason_of_failure
    )
    from line;
    
    <EXPENSE_INFO>A</EXPENSE_INFO>
    <EXPENSE_DATE>2012-12-14</EXPENSE_DATE>
    <EXPENSE_CODE>ABC</EXPENSE_CODE>
    <EXPENSE_REF>HOTEL</EXPENSE_REF>
    <GROSS_AMOUNT>100</GROSS_AMOUNT>
    <TAX_AMOUNT>500</TAX_AMOUNT>
    <ERROR_CODE>0</ERROR_CODE>
    <REASON_OF_FAILURE>SUCCESS</REASON_OF_FAILURE>
    ...
    Now you want to wrap this up in EXPENSE_LINE_REC
    select xmlelement("EXPENSE_LINE_REC",
      xmlforest(
        expense_info, expense_date, expense_code, expense_ref, 
        gross_amount, tax_amount, error_code, reason_of_failure
      )
    )
    from line;
    
    <EXPENSE_LINE_REC>
      <EXPENSE_INFO>A</EXPENSE_INFO>
      <EXPENSE_DATE>2012-12-14</EXPENSE_DATE>
      <EXPENSE_CODE>ABC</EXPENSE_CODE>
      <EXPENSE_REF>HOTEL</EXPENSE_REF>
      <GROSS_AMOUNT>100</GROSS_AMOUNT>
      <TAX_AMOUNT>500</TAX_AMOUNT>
      <ERROR_CODE>0</ERROR_CODE>
      <REASON_OF_FAILURE>SUCCESS</REASON_OF_FAILURE>
    </EXPENSE_LINE_REC>
    So far, you have one XML fragment per row in the LINE table. You can use XMLAGG to put those together in one fragment - or rather, one fragment per EXPENSE_INFO.

    Then you use XMLELEMENT again to give the name "EXPENSE_LINES" to that fragment.

    Then you gather the HEADER values and the "EXPENSE_LINES" fragment in "EXPENSE_DETAILS". That gives you all the information for each HEADER row and the associated LINE rows.

    Then you need to gather all the HEADER information into one with XMLAGG, and give all that a name with XMLELEMENT
    select xmlelement("EXPENSES",
      xmlagg(
        xmlelement("EXPENSE_DETAILS",
          xmlforest(expense_info, a.error_code, a.reason_of_failure),
          xmlelement("EXPENSE_LINES",
            xmlagg(
              xmlelement("EXPENSE_LINE_REC",
                xmlforest(
                  b.expense_date, b.expense_code, b.expense_ref, 
                  b.gross_amount, b.tax_amount, b.error_code, b.reason_of_failure
                )
              )
            )
          )
        )
      )
    )
    from header a join line b using(expense_info)
    group by expense_info, a.error_code, a.reason_of_failure;
    _Note:_ With the GROUP BY clause, you can "aggregate" twice, once at the expense_info level and again overall. That is what I do with the two XMLAGGs: the second one just puts all the different EXPENSE_DETAILS stuff together into one package that I wrap the "EXPENSES" tag around.

    Also, I omit EXPENSE_INFO from each LINE because it is already in the header information.

    Edited by: Stew Ashton on Dec 17, 2012 2:03 PM
  • 2. Re: Generating XML from Oracle tables
    884492 Newbie
    Currently Being Moderated
    Stew,

    Thank you very much for this. I am very grateful to you.


    Regards,
    Sudhanshu

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points