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