This discussion is archived
6 Replies Latest reply: Aug 27, 2013 8:59 AM by 799706 RSS

XML to CSV conversion

799706 Newbie
Currently Being Moderated

Guys,

 

I got one XML file in cXML format from client which he wants me to covert into CSV file. He would like to process this file into Oracle Applications using SQL loader. He had provided me with his xml file which I am pasting below. I need your assistance to convert XML file into CSV file. I had gone through number of threads over here but nothing could work for me since my xml file is little different than usual.

 

 

<?xml version="1.0" ?>

  <!DOCTYPE cXML (View Source for full doctype...)>

- <cXML payloadID="2013-06-25T09:37:47+10:00.0ff0d280-dd27-11e2-a2c9-cdcfaab2f763.5396284@syd1app38.ce.corp" timestamp="2013-06-25T09:37:47+10:00" version="1.2.008">

- <Header>

- <From>

- <Credential domain="NetworkID">

  <Identity>AN01000001000</Identity>

  </Credential>

- <Credential domain="DUNS">

  <Identity>750412066</Identity>

  </Credential>

  </From>

- <To>

- <Credential domain="NetworkID">

  <Identity>751594100</Identity>

  </Credential>

- <Credential domain="DUNS">

  <Identity>751594100</Identity>

  </Credential>

  </To>

- <Sender>

- <Credential domain="NetworkID">

  <Identity>AN01000001000</Identity>

  <SharedSecret>(not telling)</SharedSecret>

  </Credential>

- <Credential domain="DUNS">

  <Identity>750412000</Identity>

  </Credential>

  <UserAgent>Corporate Express webMethods HTTP(S) client</UserAgent>

  </Sender>

  </Header>

- <Request deploymentMode="production">

- <InvoiceDetailRequest>

- <InvoiceDetailRequestHeader invoiceID="9008271000" purpose="standard" operation="new" invoiceDate="2013-06-25T00:00:00+10:00">

  <InvoiceDetailHeaderIndicator />

  <InvoiceDetailLineIndicator isTaxInLine="yes" />

- <InvoicePartner>

- <Contact role="remitTo">

  <Name xml:lang="en">XYZ Limited</Name>

- <PostalAddress>

  <Street>PRIVATE BAG 16</Street>

  <City>Alexandria</City>

  <State>NSW</State>

  <PostalCode>1435</PostalCode>

  <Country isoCountryCode="AU">AU</Country>

  </PostalAddress>

  </Contact>

  <IdReference identifier="94000721000" domain="gstID" />

  </InvoicePartner>

- <InvoicePartner>

- <Contact role="soldTo">

  <Name xml:lang="en">Customer Name</Name>

- <PostalAddress>

  <Street>PO BOX a170</Street>

  <Street>ATTN ACCOUNTS PAYABLE</Street>

  <City>SOUTH City</City>

  <State>VIC</State>

  <PostalCode>3205</PostalCode>

  <Country isoCountryCode="AU">AU</Country>

  </PostalAddress>

  </Contact>

  <IdReference identifier="78061067678" domain="gstID" />

  </InvoicePartner>

- <InvoicePartner>

- <Contact role="billTo">

  <Name xml:lang="en">Customrs Name</Name>

- <PostalAddress>

  <Street>PO BOX a170</Street>

  <Street>ATTN ACCOUNTS PAYABLE</Street>

  <City>SOUTH  City</City>

  <State>VIC</State>

  <PostalCode>3205</PostalCode>

  <Country isoCountryCode="AU">AU</Country>

  </PostalAddress>

  </Contact>

  <IdReference identifier="78061067678" domain="gstID" />

  </InvoicePartner>

- <InvoiceDetailShipping>

- <Contact role="shipFrom">

  <Name xml:lang="en">Staples Australia Pty Limited</Name>

  </Contact>

- <Contact role="shipTo" addressID="VIC">

  <Name xml:lang="en" />

- <PostalAddress>

  <Street>Clothing Store,Blamey Barracks,Buil</Street>

  <City>KAPOOKA</City>

  <State>NSW</State>

  <PostalCode>2661</PostalCode>

  <Country isoCountryCode="AU">AU</Country>

  </PostalAddress>

  </Contact>

  </InvoiceDetailShipping>

  <Extrinsic name="documentType">Tax Invoice</Extrinsic>

  </InvoiceDetailRequestHeader>

- <InvoiceDetailOrder>

- <InvoiceDetailOrderInfo>

- <OrderReference orderID="SER_6776">

  <DocumentReference payloadID="20132721.5508.1900762829@IPOS02" />

  </OrderReference>

  <SupplierOrderInfo orderID="8855712905" />

  </InvoiceDetailOrderInfo>

- <InvoiceDetailItem invoiceLineNumber="000110" quantity="1.000">

  <UnitOfMeasure>BX</UnitOfMeasure>

- <UnitPrice>

  <Money currency="AUD">21.82</Money>

  </UnitPrice>

- <InvoiceDetailItemReference lineNumber="11">

- <ItemID>

  <SupplierPartID>18839409</SupplierPartID>

  </ItemID>

  <Description xml:lang="en">BIC Cristal Easy Glide Medium Black Box</Description>

  </InvoiceDetailItemReference>

- <SubtotalAmount>

  <Money currency="AUD">21.82</Money>

  </SubtotalAmount>

- <Tax>

  <Money currency="AUD">2.18</Money>

  <Description xml:lang="en">GST</Description>

- <TaxDetail purpose="tax" category="gst" percentageRate="10.000">

- <TaxAmount>

  <Money currency="AUD">2.18</Money>

  </TaxAmount>

  </TaxDetail>

  </Tax>

- <GrossAmount>

  <Money currency="AUD" />

  </GrossAmount>

  <Comments xml:lang="en" />

  </InvoiceDetailItem>

  </InvoiceDetailOrder>

- <InvoiceDetailSummary>

- <SubtotalAmount>

  <Money currency="AUD">21.82</Money>

  </SubtotalAmount>

- <Tax>

  <Money currency="AUD">2.18</Money>

  <Description xml:lang="en">GST</Description>

  </Tax>

- <SpecialHandlingAmount>

  <Money currency="AUD">0</Money>

  </SpecialHandlingAmount>

- <ShippingAmount>

  <Money currency="AUD">0</Money>

  </ShippingAmount>

- <InvoiceDetailDiscount>

  <Money currency="AUD" />

  </InvoiceDetailDiscount>

- <NetAmount>

  <Money currency="AUD">24.00</Money>

  </NetAmount>

- <DueAmount>

  <Money currency="AUD">24.00</Money>

  </DueAmount>

  </InvoiceDetailSummary>

  </InvoiceDetailRequest>

  </Request>

  </cXML>

 

 

Waiting for your reply. Thanks.

 

-Sunil

  • 1. Re: XML to CSV conversion
    odie_63 Guru
    Currently Being Moderated

    Hi,

     

    What tools do you have at your disposal?

    You've posted in the PL/SQL XML Programming section, does it mean you want to use PL/SQL for this requirement?

     

    Personally, I would try XSLT.

    The complexity here is that your input XML has multiple nested and uncorrelated levels, so the important question is : what does the resulting CSV look like?

  • 2. Re: XML to CSV conversion
    799706 Newbie
    Currently Being Moderated

    Hi Odie,

     

    My first priority is to generate CSV using PL/SQL.

     

    As you have said this XML has multiple uncorrelated level you would like to go for XSLT. It will be help if you let me know that approach using XSLT.

     

    As far as your clarification around "What CSV looks like?"  I must tell you this XML has Header and Details Data it should display Header and Line information in a single line of CSV.

     

    Thanks a lot for your response I had gone through many threads where Odie gave correct solution. I believe you can certainly help me in this regard. Thanks.

     

    -Sunil

  • 3. Re: XML to CSV conversion
    odie_63 Guru
    Currently Being Moderated
    As far as your clarification around "What CSV looks like?"  I must tell you this XML has Header and Details Data it should display Header and Line information in a single line of CSV.

    It's not that simple.

    For example, take the <InvoicePartner> element, there are 3 occurrences in the sample XML. How do they translate into csv format ? All three on the same line, or each in its own new line (potentially generating lots of redundancies in the process) ?

     

    I encourage you to do this exercise yourself first : try to build your expected csv output "manually" from the sample XML and see if the result makes sense.

    If it does then post it here and we'll see how to implement the transformation.

  • 4. Re: XML to CSV conversion
    Brian Bontrager Expert
    Currently Being Moderated

    Is the CSV file the ultimate goal, or is loading the data into the database/application the ultimate goal? 

     

    If the end goal is loading the data (and the CSV is used for nothing else later), then creating a CSV and using SQL*Loader to get it into the database is one approach; but if you are using PL/SQL to parse the XML a more direct approach would be to skip the CSV altogether: parse AND load in one PL/SQL process: parse-load-done.  Otherwise you bring the data into the database, use PL/SQL to generate a CSV outside the database, then use SQL*loader to bring that data back into the database again.

  • 5. Re: XML to CSV conversion
    odie_63 Guru
    Currently Being Moderated

    I would tend to agree with Brian here.

    It doesn't make much sense to transform XML into CSV from within the DB, just to produce another file that's meant to be loaded again in the DB.

     

    If you've read a few threads from this forum then you should know we can very well load directly the XML file without intermediate transformations.

  • 6. Re: XML to CSV conversion
    799706 Newbie
    Currently Being Moderated

    Thanks guys. Sorry for the delayed reponse. I actually followed BrainBontrager' solution I parsed XML file and loaded the data into DB table which served my purpose. Thank you guys.

     

    -Sunil

Legend

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