How to take XML data file and import to custom DB Table using DB Loader

Cathy Wilbur

Well Known Member
We use DB loader to load all of our A/P and G/L interface files to custom DB tables inside of One World E1. Until now the DB Loader process has been straight forward. All of our files are either in TXT or CSV format. For all our Interfaces we use Table Conversion Tool to read our custom DB table and loop thru the records to create a batch in the F0411Z1 file (A/P) and the F0911Z1 file (G/L & A/P).

We have a new third party application. The file they are providing to us as an interface file is changing formats from TXT to XML.

The XML file has lots of sections we do not need. We asked that this XML file be modified to just send the sections we currently need and that was not an option.

We have been searching on the web for a way to take an XML file to upload data using DB Loader in Oracle
1) Want to only load certain data sections.
2) Want to only load one item within some data sections.
3) Want to totally skip some data sections.
4) Need to manipulate some data.
5) Need to set some fields based on an if-then-else statement (ie CDN then set batch number to yyyymmddhhMMCDN; otherwise set batch number to yyyymmddhhMMUSD)
6) Want to create single rows of data in custom table for each non zero amount.
7) Understand their might be a problem if an individual record is larger than 2,000 or 4,000 bytes.
8) Oracle table we are loading is a simple Oracle DB table in One World E1 it is NOT an XML DB table.

Has anyone out there done an XML DB Load in Oracle successfully.

Has anyone had the need to take data from an XML file and load it into a custom table thru another means?

We have been doing many searches thru google on the web without much success so far.

Any help would be greatly appreciated.
 
Last edited:

franck_blettner

Active Member
Hi Cathy,

I've seen that your are on unix server. One way could be to use XSLT to transform data from XML to TXT files and after load to Oracle with a DB Load. I already do that and it works well. Have a look on xsltproc (binary tools that apply xsl template to an XML document).
You will find many reference on google about xslt language.

Regards

Franck
 

Cathy Wilbur

Well Known Member
Franck,
Would you happen to have an example of xsltproc coding. I went on the web and checked it out. Want to know where I code this xsltproc logic. Can it be coded as unix script inside shell script file?
 
Discover how to build no-code data integrations and business process automations.

franck_blettner

Active Member
Cathy,

yes sure like this :
xsltproc -o $FILES_HOME/scripts/temp/$inputxmlfile $FILES_HOME/scripts/xslt/facture.xsl $outputfile

and and example of xsl template : facture.xsl (this example read an xml file and create a csv to upload oracle table via sqlloader) :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:eek:utput method="text" indent="no"/>
<!-- Identity template -->
<!-- from http://www.w3.org/TR/xslt section 7.5 -->

<xsl:template match="/">
<xsl:copy>
<xsl:apply-templates select="//Index_S12"/>
</xsl:copy>
</xsl:template>

<xsl:template match="Index_S12">
<xsl:value-of select="./ID_DU_DOCUMENT_ID48"/><xsl:text>;</xsl:text>
<xsl:value-of select="./TYPE_DOC_JDE_ID82"/><xsl:text>;</xsl:text>
<xsl:value-of select="./CODE_SOCIETE_ID80"/><xsl:text>;</xsl:text>
<xsl:value-of select="./ACTIVITE_ID3"/><xsl:text>;</xsl:text>
<xsl:value-of select="./TYPE_PIECE_ID5"/><xsl:text>;</xsl:text>
<xsl:value-of select="./NUM_CLIENT_ID41"/><xsl:text>;</xsl:text>
<xsl:value-of select="number(translate(./ARCHI_MONTANT_ID60,',','.'))*100"/><xsl:text>;</xsl:text>
<xsl:value-of select="./ARCHI_DATE_PIECE_ID62"/><xsl:text>;</xsl:text>
<xsl:value-of select="./ARCHI_DATE_ECHE_ID66"/><xsl:text>;</xsl:text>
<xsl:text>;SQLLDR;;SQL;HOSTJDE;
</xsl:text>
</xsl:template>

</xsl:stylesheet>
 

Cathy Wilbur

Well Known Member
Thank-you, very good example. Now I understand what you are doing and what you put in a unix script file and what you put into template files.
1) So I code a script file which calls this xsl template.
2) Define an xsl template file like your facture.xsl example
3) Once the file is converted to CSV/TXT then I can import that CSV/TXT $outputfile into DB loader.

Will try it out. You recommended I convert the file as a TXT file but they wanted it converted into a CSV file.

So will use your example but convert it to CSV instead.

Have been using Google on web over last few days to find everything I can about xsltproc

Will post my working xsl template once I am done.
 
Last edited:

Cathy Wilbur

Well Known Member
How do I determine from this information sent to me what version of xslt I have? Also how do I determine if I have XPath and which version I have? Person that installed xsltproc on unix machine sent me the following.

============================================================
xsltproc will give you the details on the compiled version as follows:

xsltproc -version
Using libxml 20901, libxslt 10128 and libexslt 817
xsltproc was compiled against libxml 20632, libxslt 10128 and libexslt 817
libxslt 10128 was compiled against libxml 20632
libexslt 817 was compiled against libxml 20632

other than that I can tell you the installed version is: libxslt-1.1.28-2

I have no idea what XPath is but you are running on libxml2-2.9.1-1 and from a quick internet search I believe it it a built in part of that library
============================================================
 

Cathy Wilbur

Well Known Member
Here is XML file coming out of an Alma Library system - do not need all these fields just highlighted fields required. Started writing the xml code below. Problem is that the XML is so complex to extract the small amount of data that I need. I put all then non-recurring data at the beginning and the recurring data at the end. Want to know how to do the following.

1) How do I put derived strings that do not necessarily come from am XML field as a field?
2) How do I properly loop thru my Fund_Info section within each Invoice and write out a new line.
3) Is this code going to properly extract the data I need from the XML file into a CSV file.

4) Writing unix script to do the following (script all written - just having some issues coding xsl procedure due to complexity of XML file)
a) Archive XML file
b) Convert XML file into CSV
c) Delete prior loader files
d) Use Oracle DB loader to load CSV file into a DB Table.
e) Our application software will then process the input data from the DB table to generate the General Ledger (GL) and Accounts Payable (AP) batches in Finance

Below I created 3 sections.
I. XML file with one invoice in it.
II. XSLT conversion code I have written - not complete - (have questions about extracting different sections of data from a complex XML file.
III. CSV Data output I need to extract from XML file.


I. XML File (areas highlighted in blue are what I need from file)
<?xml version="1.0"?>
<payment_data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://com/exlibris/repository/acq/invoice/xmlbeans">
<invoice_list>
<invoice>
<invoice_number>PO-305</invoice_number>
<approved_by>Belanger Patricia</approved_by>
<invoice_amount>
<currency>USD</currency>
<sum>22</sum>
</invoice_amount>
<vendor_code>ABE</vendor_code>
<vendor_name>ABEBOOKS</vendor_name>
<vendor_FinancialSys_Code xsi:nil="true"/>
<vendor_liable_for_vat>false</vendor_liable_for_vat>
<vendor_payment_address_list>
<payment_address preferred="true">
<line1>ABE Overall Vendor Address</line1>
<line2 xsi:nil="true"/>
<line3 xsi:nil="true"/>
<line4 xsi:nil="true"/>
<line5 xsi:nil="true"/>
<city>Toronto</city>
<country>CAN</country>
<note>adding overall address</note>
<postalCode xsi:nil="true"/>
<stateProvince>ON</stateProvince>
<types>
<type> Billing</type>
<type>Claim</type>
<type>Order</type>
<type>Payment</type>
<type>Returns</type>
<type>Shipping</type>
</types>
</payment_address>
</vendor_payment_address_list>
<unique_identifier>2014082450002181</unique_identifier>
<invoice_date>07/24/2014</invoice_date>
<payment_method>ACCOUNTINGDEPARTMENT</payment_method>
<noteList>
<note>
<content>PrePaid</content>
<owneredEntity>
<createdBy>Sandboxpbelanger</createdBy>
<creationDate>20140724</creationDate>
<customerId>2180</customerId>
<institutionId>2181</institutionId>
<modificationDate>20140724</modificationDate>
<modifiedBy>Sandboxpbelanger</modifiedBy>
</owneredEntity>
</note>
</noteList>
<vat_info>
<expended_from_fund_ind>true</expended_from_fund_ind>
<inclusive_ind>false</inclusive_ind>
<vat_amount>0.0</vat_amount>
<vat_percentage>0.0</vat_percentage>
<vat_type>EXCLUSIVE</vat_type>
<vat_in_invoice_line_level>false</vat_in_invoice_line_level>
<vat_description xsi:nil="true"/>
<vat_code xsi:nil="true"/>
</vat_info>
<additional_charges>
<discount_amount>0.0</discount_amount>
<insurance_amount>0.0</insurance_amount>
<overhead_amount>0.0</overhead_amount>
<shipment_amount>0.0</shipment_amount>
<total_charges_amount>0.0</total_charges_amount>
</additional_charges>
<invoice_line_list>
<invoice_line>
<line_number>1</line_number>
<line_type>REGULAR</line_type>
<quantity>1</quantity>
<reporting_code>PRINTBOOK</reporting_code>
<total_price>20.0</total_price>
<price>20.0</price>
<po_line_info>
<fully_Invoiced_ind>true</fully_Invoiced_ind>
<po_line_owner>Leddy Library*</po_line_owner>
<mms_record_id>99178220802181</mms_record_id>
<meta_data_values>
<acqterms_identifier>0199669937</acqterms_identifier>
<acqterms_identifierType>ISBN</acqterms_identifierType>
<acqterms_place>Oxford :</acqterms_place>
<creator>Mayer, C. P. (Colin P.)</creator>
<date>2013.</date>
<publisher>Oxford University Press</publisher>
</meta_data_values>
<vendor_note xsi:nil="true"/>
<vendor_reference_number>order number 102</vendor_reference_number>
<additional_order_reference xsi:nil="true"/>
<po_line_number>POL-411</po_line_number>
<po_line_price>20.0</po_line_price>
<po_line_title>Firm commitment : why the corporation is failing us and how to restore trust in it / Colin Mayer.</po_line_title>
</po_line_info>
<fund_info_list>
<fund_info>
<amount>
<currency>USD</currency>
<sum>20</sum>
</amount>
<code>BIOL-MON-1</code>
<fiscal_period>2014-2015</fiscal_period>
<external_id>18105.8820</external_id>
<type>ALLOCATED</type>
</fund_info>
</fund_info_list>
</invoice_line>
<invoice_line>
<line_number>999991</line_number>
<line_type>SHIPMENT</line_type>
<quantity>1</quantity>
<total_price>2.0</total_price>
<price>2.0</price>
<fund_info_list>
<fund_info>
<amount>
<currency>USD</currency>
<sum>2</sum>
</amount>
<code>SHIP 8430</code>
<fiscal_period>2014-2015</fiscal_period>
<external_id>18105.8430</external_id>
<type>ALLOCATED</type>
</fund_info>
</fund_info_list>
</invoice_line>
<invoice_line>
<line_number>999992</line_number>
<line_type>DISCOUNT</line_type>
<quantity>0</quantity>
<total_price>0.0</total_price>
<price>0.0</price>
</invoice_line>
<invoice_line>
<line_number>999993</line_number>
<line_type>OVERHEAD</line_type>
<quantity>0</quantity>
<total_price>0.0</total_price>
<price>0.0</price>
</invoice_line>
<invoice_line>
<line_number>999994</line_number>
<line_type>INSURANCE</line_type>
<quantity>0</quantity>
<total_price>0.0</total_price>
<price>0.0</price>
</invoice_line>
</invoice_line_list>
<invoice_exchange_rate_list>
<exchange_rate>
<currency>CAD</currency>
<explicit_ind>false</explicit_ind>
<rate>1.0742</rate>
</exchange_rate>
</invoice_exchange_rate_list>
</invoice>
</invoice_list>
</payment_data>


II. LibrXmlToCSV - xslt (Xsl procedure to convert from XML to CSV)
< ?xml version="1.0" encoding="UTF-8"?>
< xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
< xsl:eek:utput method="text" indent="no"/>
< !-- Identity template -->
< !-- from http://www.w3.org/TR/xslt section 7.5 -->

<xsl:template match="/">
<xsl:copy>
<xsl:apply-templates select="//invoice"/>
</xsl:copy>
</xsl:template>

<xsl:template match=" invoice ">
< xsl:value-of select="./invoice_number"/><xsl:text>;</xsl:text>
< !-- ??? PymtTerms ' string dervied based on contents of Invoice_number - does not come from xml file -->
< xsl:value-of select="./hardcode_calc_paymntterms"/><xsl:text>;</xsl:text>
< xsl:value-of select=".invoice_amount/currency"/><xsl:text>;</xsl:text>
< !-- ??? BatchNumber derived - format yyyymmddhhmmCUR where current date + CUR is invoice_amount/currency value -->
< xsl:value-of select="./hardcoded_batch_number"/><xsl:text>;</xsl:text>
< xsl:value-of select="./vendor_code"/><xsl:text>;</xsl:text>
< !-- ??? FinancialCode – convert to an 8 digit supplier number if blank use zero -->
< xsl:value-of select="./vendor_FinancialSys_Code"/><xsl:text>;</xsl:text>
< !-- ??? UniqueIdentifier – convert to a number -->
< xsl:value-of select="./unique_identifier"/><xsl:text>;</xsl:text>
< !-- ??? InvoiceDate – format as yyyymmdd -->
< xsl:value-of select="./invoice_date"/><xsl:text>;</xsl:text>
< !-- ??? GLDate – format as yyyymmdd – data does not come from XML file – just use current date -->
< xsl:value-of select="./hardcoded_gl_date"/><xsl:text>;</xsl:text>
< !-- ??? UserRef ' string dervied based on contents of other data - does not come from xml file -->
< xsl:value-of select="./hardcode_userref"/><xsl:text>;</xsl:text>
< !-- ??? Name – Remark ' string dervied based on contents of other data - does not come from xml file -->
< xsl:value-of select="./hardcode_remark"/><xsl:text>;</xsl:text>
< !-- ??? ExchangeRate convert to number – set to zero if explicit_ind = false -->
< xsl:value-of select=".exchange_rate_list/exchange_rate/rate"/><xsl:text>;</xsl:text> ‘convert to number – set to zero if explicit_ind = false
< !-- ??? fund_info - using fund info to only add row if amnt <> 0 - make implied decimal -->
< xsl:value-of select="number(translate(.invoice_line/fund_info_list/fund_info/amount/sum,',','.'))*100"/><xsl:text>;</xsl:text>
‘< xsl:value-of select=".invoice_line/fund_info_list/fund_info/fiscal_period"/><xsl:text>;</xsl:text>
< xsl:value-of select=".invoice_line/fund_info_list/fund_info/external_id"/><xsl:text>;</xsl:text>
< xsl:text>;SQLLDR;;SQL;HOSTJDE;
< /xsl:text>
</xsl:template>
< /xsl:stylesheet>

III. Output - (want one output line for each invoice_line/fund_info_list/fund_info/amount)
InvoiceNumber, PymntTerm, InvoiceCurrency, BatchNo, VendorCode, FinancialSysCode, UniqueIdentifier, InvoiceDate, GLDate, UserRef, Remark, ExchgRate, FundAmnt, FiscalPeriod, GLAcct
PO-305, I, USD, yyyymmddhhmmCUR, ABE, 34567, 1234567890, yyyymmdd, yyyymmdd, UserRefString, RemarkString, 1.0742, 20, 2014-2015, 18105.8820
PO-305, I, USD, yyyymmddhhmmCUR, ABE, 34567, 1234567890, yyyymmdd, yyyymmdd, UserRefString, RemarkString, 1.0742, 2, 2014-2015, 18105.8430
 

Cathy Wilbur

Well Known Member
Here is the code I have so far. It works really good but I have to make some changes.
1) Need to know how to split my for each section so each field is on a separate line but it does not wrap the fields to separate records.
2) How do I define hardcoded values?

Get following output from code below
PO-305, 22, USD, ABE, 2014082450002181, 07/24/2014, ACCOUNTINGDEPARTMENT, 1.0742, 20, 2014-2015,18105.8820,Leddy Library*
PO-305, 22, USD, ABE, 2014082450002181, 07/24/2014, ACCOUNTINGDEPARTMENT, 1.0742, 2, 2014-2015,18105.8430,Leddy Library*


<?xml version="1.0" encoding="UTF-8" ?>

<!-- New document created with EditiX at Thu Oct 23 16:28:34 EDT 2014 -->

<xsl:stylesheet version="2.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
xmlns:xdt="http://www.w3.org/2005/xpath-datatypes"
xmlns:err="http://www.w3.org/2005/xqt-errors"
exclude-result-prefixes="xs xdt err fn">

<xsl:eek:utput method="text" indent="no"/>

<!-- OUSTANDING OUTPUT DATA ISSUES -->
<!-- xsl:value-of select="../../../../../vendor_FinancialSys_Code" />, need to check for zero or nil then use 99999 otherwise take the value -->
<!-- xsl:value-of select="../../../../../hardcode_RemarkString" />, derived field -->
<!-- xsl:value-of select="../../../../../hardcode_GLDate" />, current date in format yyyymmdd -->
<!-- xsl:value-of select="../../../../../hardcode_BatchNo" />, format yyyymmddhhmmXCUR where X is value from Line Owner (L-Leddy, W-Law), CUR is from invoice_amount/currency, yyyymmddhhmm is current date -->
<!-- xsl:value-of select="../../../../../hardcode_PayTerms" />, derived from part of invoice/invoice_number -->

<xsl:template match="/">
<xsl:text>
</xsl:text>
<xsl:for-each select="payment_data/invoice_list/invoice/invoice_line_list/invoice_line/fund_info_list/fund_info/amount">
<xsl:value-of select="../../../../../invoice_number" />, <xsl:value-of select="../../../../../invoice_amount/sum" />, <xsl:value-of select="../../../../../invoice_amount/currency" />, <xsl:value-of select="../../../../../vendor_code" />, <xsl:value-of select="../../../../../unique_identifier" />, <xsl:value-of select="../../../../../invoice_date" />, <xsl:value-of select="../../../../../payment_method" />, <xsl:value-of select="../../../../../invoice_exchange_rate_list/exchange_rate/rate" />, <xsl:value-of select="sum" />, <xsl:value-of select="../fiscal_period" />,<xsl:value-of select="../external_id" />,<xsl:value-of select="../../../../invoice_line/po_line_info/po_line_owner" />
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:template>

</xsl:stylesheet>
 

franck_blettner

Active Member
Hi Cathy,

1) Perhaps you have to not used a for-each but change your <xsl:template math>. Replace your / with the section you want to repeat.
2) Hard coded value can be <xsl:text> as you do for comma

Franck
 

Cathy Wilbur

Well Known Member
Finally got my XML to CSV conversion working in my XSL 2.0 stylesheet. Have a question about a unix command.

Want to write a command line to call Saxon processor from a unix script line. Am using Saxon from Unix command line to convert my XML file into a CSV file so it can be uploaded into a DB Table. We are using the DB table as input into our AP interface in ERP E1. Had to switch to Saxon processor because xsltproc does not work with xsl 2.0.

These are the two examples that were recommended to me below. In the command line call I need to specify two classpaths
Classpath for JAVA
Classpath for SAXON jar files

How would I call the saxon processor in a unix script and pass it the following information
JavaClassPath, SaxonJarClassPath, InputFile, StylesheetFile, OutputFile

Someone gave met these two command line examples.

1) saxon –r classname -s:LibrInvoice.xml -xsl: stylesheet.xsl –o output.csv

2) java -jar </path/to/saxon.jar> <xmlfile> <xslfile> -o:</path/to/outputfile.csv>
 
Last edited:
Top