[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

[xsl] Fw: How do I properly extract data from different sections of a complicated XML file - converting from XML to CSV

---

---

Problem I have is that the XML is so complex to extract the small amount
of data that I need but this is file application generates.  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.  Can I set up derived fields where I have to manipulate the string
to generate the string?
2)  How do I properly loop thru my Fund_Info section within each Invoice
and write out a new line each time I get a new Fund info 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.  (file was too large to put
in the email)
        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 file has too many sections to display here

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:output 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 b convert to an 8 digit supplier number if blank
use zero -->
< xsl:value-of select="./vendor_FinancialSys_Code"/><xsl:text>;</xsl:text>
< !-- ??? UniqueIdentifier b convert to a  number -->
< xsl:value-of select="./unique_identifier"/><xsl:text>;</xsl:text>
< !-- ??? InvoiceDate b format as yyyymmdd -->
< xsl:value-of select="./invoice_date"/><xsl:text>;</xsl:text>
< !-- ??? GLDate b format as yyyymmdd b data does not come from XML file
b
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 b 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 b set to zero if explicit_ind
= false -->
< xsl:value-of
select=".exchange_rate_list/exchange_rate/rate"/><xsl:text>;</xsl:text>
bconvert to number b 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>

b< xsl:value-of
select=".invoice_line/fund_info_list/fund_info/fiscal_period"/><xsl:text>;</x
sl: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

_____________________________________________________________________
Catherine Wilbur  |  Senior Application Programmer  |  IT Services
401 Sunset Avenue, Windsor ON Canada  N9B 3P4
(T) 519.253.3000 Ext. 2745  |  (F) 519.973.7083  |  (E)
cwilbur@xxxxxxxxxxx
www.uwindsor.ca/its

---