1 2 Previous Next 18 Replies Latest reply: Mar 5, 2013 2:02 AM by 894936 RSS

    how to convert table data to XML format.

    894936
      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
          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
            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
              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
                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
                  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
                    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.
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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