This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 5, 2013 12:02 AM by 894936 RSS

how to convert table data to XML format.

894936 Newbie
Currently Being Moderated
Hi Team,
I need one help form you.
I have below table structure with the data. I want this data to be sent as XML to destination. so i want to convert this table data to XML .How do i need to convert?
BU_CODE     BU_TYPE     CUST_NO     CUR_CODE     SALES_DATE     RECEIPT_NO     TILL_NO     INVOICE_TOTAL      ON_HOLD_FLAG
312     STO          EUR     11/17/2012     104                       9     381.72              N
312           STO                          EUR             11/17/2012             199                            5             147.52                          N
312          STO                           EUR          11/17/2012                  23                           52             110.84                         N
312          STO                           EUR          11/17/2012                  63                            6                67.77                         N

***************************************
<?xml version="1.0" encoding="UTF-8" ?> 
- <tendermanagement xmlns="http://www.ikea.com/sarec/declaredfunds" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd" buType="STO" buCode="312" sourceSystem="RIMS">
- <!--  RIMS 
  --> 
  <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="381.72" tillNo="9" transactionNo="104" /> 
  <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="147.52" tillNo="5" transactionNo="199" /> 
  <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="119.84" tillNo="52" transactionNo="23" /> 
  <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="67.77" tillNo="6" transactionNo="63" /> 
  </tendermanagement>
  • 1. Re: how to convert table data to XML format.
    jeneesh Guru
    Currently Being Moderated
    Like..
    SELECT XMLElement("Emp", 
                       XMLElement("name", e.first_name ||' '|| e.last_name),
                       XMLElement("hiredate", e.hire_date)) AS "RESULT" 
    FROM hr.employees e 
    WHERE employee_id > 200 ;
    Refer the Docs
  • 2. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi,
    Actually i want to convert this data to XML file.

    select means we will be able to see in xml right. Even that i am not able to do it.

    Thanks,
    MR
  • 3. Re: how to convert table data to XML format.
    jeneesh Guru
    Currently Being Moderated
    891933 wrote:
    Hi,
    Actually i want to convert this data to XML file.
    Check for DBMS_XMLQUERY and UTL_FILE
    select means we will be able to see in xml right. Even that i am not able to do it.
    What does that mean?
    Are you getting any error?
  • 4. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi Jeneesh,
    I tried like below.
    I am getting output like INVOICE_TOTAL as one column with empty data.
    How do we look this output generally
    SELECT XMLElement("send_date", 
                       XMLElement("bu_code", e.bu_code),
                       XMLElement("invoice_total", e.invoice_total)) AS "INVOICE_TOTAL" 
    FROM table_name e 
    WHERE BU_CODE =118 ;
  • 5. Re: how to convert table data to XML format.
    jeneesh Guru
    Currently Being Moderated
    891933 wrote:
    Hi Jeneesh,
    I tried like below.
    I am getting output like INVOICE_TOTAL as one column with empty data.
    How do we look this output generally
    select *
    from test;
    
    C1 C2
    -- --
     1  2 
     3  4 
    
    SELECT XMLElement("test", 
                       xmlelement("c1", c1),
                       XMLElement("c2", c2)) AS test 
    FROM test ;
    
    TEST                                                                           
    ---------------------------------------
    <test><c1>1</c1><c2>2</c2></test>      
    <test><c1>3</c1><c2>4</c2></test>
  • 6. Re: how to convert table data to XML format.
    odie_63 Guru
    Currently Being Moderated
    No database version, no sample data in usable form... difficult to help efficiently apart from giving general pointers to the docs as Jeneesh did.


    No tested, but this should get you going :
    select xmlelement("tendermanagement",
             xmlattributes(
               'http://www.ikea.com/sarec/declaredfunds' as "xmlns"
             , 'http://www.w3.org/2001/XMLSchema-instance' as xmlns:xsi
             , 'http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd' as "xsi:schemaLocation"
             , bu_type as "buType"
             , bu_code as "buCode"
             , 'RIMS' as "sourceSystem"
             )
           , xmlagg(
               xmlelement("tendermovement",
                 xmlattributes(
                   to_char(sales_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "date"
                 , invoice_total as "valueTendered"
                 , till_no as "tillNo"
                 , receipt_no as "transactionNo"
                 )
               )
             )
           ) as xml_doc
    from <your_table>
    group by bu_code, bu_type ;
  • 7. Re: how to convert table data to XML format.
    Nitesh. Explorer
    Currently Being Moderated
    Try in this simple way :


    select dbms_xmlgen.getxml('SELECT * from EMP') from dual;


    Rgds,
    Nitkhush.
  • 8. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your help.
    I have posted my table and required xml in my first post.

    I am using oracle 10g... toool is Toad.
  • 9. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi Jeneesh,
    I tried to run in Toad.
    But iam getting only one column like TEST and no data is getting displayed.
    Do i need to change any settings in TOAD to look for XML file.

    Here iam using toad and oracle 10g.
    Sorry to trouble you.
  • 10. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi Odie,
    Below is the table for which i want the data to be displayed like below xml file
    BU_CODE     BU_TYPE     CUST_NO     CUR_CODE     SALES_DATE     RECEIPT_NO     TILL_NO     INVOICE_TOTAL      ON_HOLD_FLAG
    312     STO          EUR     11/17/2012     104                       9     381.72              N
    312           STO                          EUR             11/17/2012             199                            5             147.52                          N
    312          STO                           EUR          11/17/2012                  23                           52             110.84                         N
    312          STO                           EUR          11/17/2012                  63                            6                67.77                         N
     
    ***************************************
    <?xml version="1.0" encoding="UTF-8" ?> 
    - <tendermanagement xmlns="http://www.ikea.com/sarec/declaredfunds" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd" buType="STO" buCode="312" sourceSystem="RIMS">
    - <!--  RIMS 
      --> 
      <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="381.72" tillNo="9" transactionNo="104" /> 
      <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="147.52" tillNo="5" transactionNo="199" /> 
      <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="119.84" tillNo="52" transactionNo="23" /> 
      <tendermovement date="2012-11-17T01:00:00Z" bookingType="DROP" tenderType="B2B Invoice" salesAreaGroup="STORE" valueTendered="67.77" tillNo="6" transactionNo="63" /> 
      </tendermanagement>
    
     
     
  • 11. Re: how to convert table data to XML format.
    odie_63 Guru
    Currently Being Moderated
    Yes I've seen that already, if I haven't I wouldn't even have replied in the first place, so no need to post the same thing again.

    By "usable form" I mean something we can use to actually test our solutions, such as CREATE TABLE and INSERT statements for your sample data.

    That being said, did you try my suggestion?
  • 12. Re: how to convert table data to XML format.
    odie_63 Guru
    Currently Being Moderated
    But iam getting only one column like TEST and no data is getting displayed.
    Do i need to change any settings in TOAD to look for XML file.
    I don't use TOAD but I know some older versions don't support XMLType (that's the datatype returned by the query).
    Can't you test it in SQL*Plus instead?

    I've added the .getClobVal() method to convert the XMLType to CLOB :
    select xmlelement("tendermanagement",
             xmlattributes(
               'http://www.ikea.com/sarec/declaredfunds' as "xmlns"
             , 'http://www.w3.org/2001/XMLSchema-instance' as xmlns:xsi
             , 'http://www.prod.com/S_Dest/declaredfunds declaredFunds.xsd' as "xsi:schemaLocation"
             , bu_type as "buType"
             , bu_code as "buCode"
             , 'RIMS' as "sourceSystem"
             )
           , xmlagg(
               xmlelement("tendermovement",
                 xmlattributes(
                   to_char(sales_date, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') as "date"
                 , invoice_total as "valueTendered"
                 , till_no as "tillNo"
                 , receipt_no as "transactionNo"
                 )
               )
             )
           ).getclobval() as xml_doc
    from <your_table>
    group by bu_code, bu_type ;
  • 13. Re: how to convert table data to XML format.
    894936 Newbie
    Currently Being Moderated
    Hi ,

    I tried this select statement mentioned by you . Its worked fine.
    But when i am getting the records i am getting the output like
    XML_DOC
    (HUGECLOB)
    (HUGECLOB)
    (HUGECLOB)
    (HUGECLOB)
    (HUGECLOB)
    When i open this (HUGECLOB) FILE i am getting the data displayed in single line.
    unable to see data properly.
    could you please tell me how to see the data properly.
  • 14. Re: how to convert table data to XML format.
    odie_63 Guru
    Currently Being Moderated
    891933 wrote:
    unable to see data properly.
    could you please tell me how to see the data properly.
    Depends on what you mean by "properly".

    Is the generated XML document incomplete?

    If you need to see it in a nicely formatted output with indentation and newlines, then just save the doc in a file and open it with an XML editor (any recent web browser will do).
    Alternatively, in your version, use the EXTRACT() method, it will force pretty-print, but I suggest you only do that during development, for debugging purpose :
    ... ).extract('/*').getclobval() as xml_doc
1 2 Previous Next

Legend

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