This discussion is archived
4 Replies Latest reply: Oct 10, 2012 3:22 AM by gauty RSS

XML Loading into CSV or table

gauty Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    This works like Magic...
    Thank you so much !!!!! :) :)

Legend

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