Subject: [xsl] Fw: How do I properly extract data from different sections of a complicated XML file - converting from XML to CSV From: "Catherine Wilbur cwilbur@xxxxxxxxxxx" <xsl-list-service@xxxxxxxxxxxxxxxxxxxxxx> Date: Tue, 21 Oct 2014 15:37:29 -0000 |
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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
Re: [xsl] How do I determine which , Bridger Dyson-Smith | Thread | Re: [xsl] Fw: How do I properly ext, Wendell Piez wapiez@ |
Re: [xsl] How do I determine which , Bridger Dyson-Smith | Date | [xsl] How do I properly extract dat, Catherine Wilbur cwi |
Month |