4 Replies Latest reply: Oct 10, 2012 5:22 AM by gauty RSS

    XML Loading into CSV or table

    gauty
      Hi,
      I am on Oracle Database 11G R2 working on an XML Parsing technique.

      I have an XML that I have actually parsed. The Namespace "n0" has a tag called "Rows" and the same namespace has a tag called "Columns".
      The value for that column will be the corresponding value in the "row" tag separated by commas as shown below.
      The XML Parsing is working fine. But the rows are being obtained as appended chunk of string.
      I need to map them to their corresponding columns and insert them into that table.
      Can anybody please help me how do I go about accomplishing it?
      I am pasting the XML Parsing Logic Code below.


      with T as
      (SELECT xmltype('<?xml version="1.0" encoding="UTF-8"?>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
      <soapenv:Header />
      <soapenv:Body>
      <n0:RunAnalyticsReportResponse xmlns:n0="urn:messages.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <n0:CSVTableSet>
      <n0:CSVTables>
      <n0:CSVTable>
      <n0:Name>MA Email Stats - KB</n0:Name>
      <n0:Columns>Mailing,Message,Document,Flow ID,Web Page ID,Tracked Link,Contact ID,Referring Contact ID,Date Created,Email Address,Document,Clicked,Total Clicked,Opened,Total Opened,Friend Submits,Friend Clicks,Hard Bounces,Soft Bounces,Other Bounces,Mailing,Message,Sent,Type,SPAM Loop Unsubscribes,List Header Unsubscribes,Unsubscribed</n0:Columns>
      <n0:Rows>
      <n0:Row>,,,1,,,1,,''2012-07-30 14:53:14'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,1,,''2012-08-02 07:24:01'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,1,,''2012-08-09 07:18:06'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,''2012-08-16 03:50:26'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,''2012-08-16 04:00:01'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,''2012-08-16 05:08:02'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,''2012-08-16 06:08:11'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,''2012-08-30 17:10:06'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,''2012-09-14 00:53:01'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,''2012-09-14 01:02:39'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      </n0:Rows>
      </n0:CSVTable>
      </n0:CSVTables>
      </n0:CSVTableSet>
      </n0:RunAnalyticsReportResponse>
      </soapenv:Body>
      </soapenv:Envelope>') p_xml FROM dual
      )
      SELECT
      x.report_name
      from t,
      xmltable( xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as "soapenv" ,'urn:messages.ws.rightnow.com/v1_2' as "n0" ) , '/soapenv:Envelope/soapenv:Body/n0:RunAnalyticsReportResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable' passing t.p_xml columns report_name clob path 'n0:Rows' ) x;

      --------------------------------------------------------------------------------------------

      This is the original XML Response that I obtained.

      <?xml version="1.0" encoding="UTF-8"?>
      <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
      <soapenv:Header />
      <soapenv:Body>
      <n0:RunAnalyticsReportResponse xmlns:n0="urn:messages.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <n0:CSVTableSet>
      <n0:CSVTables>
      <n0:CSVTable>
      <n0:Name>MA Email Stats - KB</n0:Name>
      <n0:Columns>Mailing,Message,Document,Flow ID,Web Page ID,Tracked Link,Contact ID,Referring Contact ID,Date Created,Email Address,Document,Clicked,Total Clicked,Opened,Total Opened,Friend Submits,Friend Clicks,Hard Bounces,Soft Bounces,Other Bounces,Mailing,Message,Sent,Type,SPAM Loop Unsubscribes,List Header Unsubscribes,Unsubscribed</n0:Columns>
      <n0:Rows>
      <n0:Row>,,,1,,,1,,'2012-07-30 14:53:14',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,1,,'2012-08-02 07:24:01',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,1,,'2012-08-09 07:18:06',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,'2012-08-16 03:50:26',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,'2012-08-16 04:00:01',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,19,,'2012-08-16 05:08:02',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,'2012-08-16 06:08:11',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,'2012-08-30 17:10:06',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,'2012-09-14 00:53:01',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      <n0:Row>,,,1,,,26,,'2012-09-14 01:02:39',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
      </n0:Rows>
      </n0:CSVTable>
      </n0:CSVTables>
      </n0:CSVTableSet>
      </n0:RunAnalyticsReportResponse>
      </soapenv:Body>
      </soapenv:Envelope>
        • 1. Re: XML Loading into CSV or table
          odie_63
          That's interesting.

          How do you know which column item from <n0:Columns> corresponds to which real column name in the target table?
          • 2. Re: XML Loading into CSV or table
            AlexAnd
            you can generate csv like
            declare
            
            v_file  UTL_FILE.FILE_TYPE;
            
            begin
            
            v_file := UTL_FILE.FOPEN(location     => 'MYDIR',
                                       filename     => 'to_csv.csv',
                                       open_mode    => 'w',
                                       max_linesize => 32767);
              FOR cur_rec IN (
            
            with T as
             (SELECT xmltype('<?xml version="1.0" encoding="UTF-8"?>
            <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
            <soapenv:Header />
            <soapenv:Body>
            <n0:RunAnalyticsReportResponse xmlns:n0="urn:messages.ws.rightnow.com/v1_2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
            <n0:CSVTableSet>
            <n0:CSVTables>
            <n0:CSVTable>
            <n0:Name>MA Email Stats - KB</n0:Name>
            <n0:Columns>Mailing,Message,Document,Flow ID,Web Page ID,Tracked Link,Contact ID,Referring Contact ID,Date Created,Email Address,Document,Clicked,Total Clicked,Opened,Total Opened,Friend Submits,Friend Clicks,Hard Bounces,Soft Bounces,Other Bounces,Mailing,Message,Sent,Type,SPAM Loop Unsubscribes,List Header Unsubscribes,Unsubscribed</n0:Columns>
            <n0:Rows>
            <n0:Row>,,,1,,,1,,''2012-07-30 14:53:14'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,1,,''2012-08-02 07:24:01'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,1,,''2012-08-09 07:18:06'',abc@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,19,,''2012-08-16 03:50:26'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,19,,''2012-08-16 04:00:01'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,19,,''2012-08-16 05:08:02'',def@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,26,,''2012-08-16 06:08:11'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,26,,''2012-08-30 17:10:06'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,26,,''2012-09-14 00:53:01'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            <n0:Row>,,,1,,,26,,''2012-09-14 01:02:39'',xyz@gmail.com,,0,0,0,0,0,0,0,0,0,,,0,Entry Point,0,0,0</n0:Row>
            </n0:Rows>
            </n0:CSVTable>
            </n0:CSVTables>
            </n0:CSVTableSet>
            </n0:RunAnalyticsReportResponse>
            </soapenv:Body>
            </soapenv:Envelope>') p_xml
                FROM dual)
            SELECT x.columns_name as dat
              from t,
                   xmltable(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as
                                          "soapenv",
                                          'urn:messages.ws.rightnow.com/v1_2' as "n0"),
                            '/soapenv:Envelope/soapenv:Body/n0:RunAnalyticsReportResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable'
                            passing t.p_xml columns columns_name varchar2(4000) path 'n0:Columns'  
                            ) x
            union all                
            SELECT x.row_data as dat
              from t,
                   xmltable(xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' as
                                          "soapenv",
                                          'urn:messages.ws.rightnow.com/v1_2' as "n0"),
                            '/soapenv:Envelope/soapenv:Body/n0:RunAnalyticsReportResponse/n0:CSVTableSet/n0:CSVTables/n0:CSVTable/n0:Rows/n0:Row'
                            passing t.p_xml columns row_data varchar2(4000) path '.'  
                            ) x
            )                
            LOOP
                UTL_FILE.PUT_LINE(v_file, cur_rec.dat);
              END LOOP;
              UTL_FILE.FCLOSE(v_file);
              
            EXCEPTION
              WHEN OTHERS THEN
                UTL_FILE.FCLOSE(v_file);
                RAISE;
            END;
            • 3. Re: XML Loading into CSV or table
              gauty
              I will prepare the table with exactly the same columns .. and while parsing the columns in XML, will do the replace function on "space" with an underscore ( "_" ) ..
              and map it to corresponding column..
              I am not sure if its the right approach though..
              • 4. Re: XML Loading into CSV or table
                gauty
                This works like Magic...
                Thank you so much !!!!! :) :)