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

Re: [xsl] Code used to extract data from complex XML file - how do you move fields value of select fields to separate lines


Subject: Re: [xsl] Code used to extract data from complex XML file - how do you move fields value of select fields to separate lines
From: "Alan Painter alan.painter@xxxxxxxxx" <xsl-list-service@xxxxxxxxxxxxxxxxxxxxxx>
Date: Fri, 24 Oct 2014 23:39:53 -0000

Hi Catherine,

I understand from your previous mails that you are looking for a way to
transform from XML to CSVs.  There is a lot of that going around and I've
dabbled in that a bit myself.

What is somewhat interesting about your input document, although hardly
unique, is that there are lists of lists in it.  For instance, the top
level is an invoice_list and this, in turn, contains a fund_list, so a list
inside of a list. I'm not sure exactly how you want to turn this into a CSV
for the few elements that you indicated in your original mail, but I can
make a suggestion that may give you some ideas to go on.

I'm not suggesting that this is an ultimate solution nor even correct, but
it may give you some forms to work on:

best of luck

-aljo

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    version="2.0">

    <xsl:output method="text" indent="no"/>

    <xsl:template match="/" xpath-default-namespace="
http://com/exlibris/repository/acq/invoice/xmlbeans">

        <xsl:for-each select="payment_data/invoice_list/invoice">

            <xsl:variable name="invoice" select="current()" />

            <xsl:for-each select="invoice_line_list/invoice_line">

                <xsl:variable name="invoice_line" select="current()" />

                    <xsl:for-each select="fund_info_list/fund_info">

                        <xsl:variable name="fund_info" select="current()" />

                        <xsl:variable name="outputColumns" as="xs:string*">

                            <xsl:sequence select="$invoice/invoice_number"
/>
                            <xsl:sequence select=
"$invoice/invoice_amount/sum" />
                            <xsl:value-of select=
"$invoice/invoice_amount/currency" />
                            <xsl:value-of select="$invoice/vendor_code" />
                            <xsl:value-of select=
"$invoice/unique_identifier" />
                            <xsl:value-of select="$invoice/invoice_date" />
                            <xsl:value-of select="$invoice/payment_method"
/>
                            <xsl:value-of select=
"($invoice/invoice_exchange_rate_list/exchange_rate/rate)[1]" />
                            <xsl:value-of select="$fund_info/amount/sum" />
                            <xsl:value-of select="$fund_info/fiscal_period"
/>
                            <xsl:value-of select="$fund_info/external_id" />
                            <xsl:value-of select=
"$invoice_line/po_line_info/po_line_owner" />
                        </xsl:variable>
                        <xsl:value-of select="string-join($outputColumns,
',')" />
                        <xsl:text>&#xD;
</xsl:text>

                    </xsl:for-each>
            </xsl:for-each>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>








On Fri, Oct 24, 2014 at 10:52 PM, Catherine Wilbur cwilbur@xxxxxxxxxxx <
xsl-list-service@xxxxxxxxxxxxxxxxxxxxxx> wrote:

> About a week ago I posted information about a complex XML file I was
> trying to convert to CSV.  I have since come up with the following XML
> stylesheet.
> Have some questions about how to tweak this stylesheet.  Had to start at
> the recurring section then move upwards to build the CSV file.
>
> *O/S Issues*
> 1)  Extracted all default fields from XML file using my XSLT code below.
> 2)  How can I put each value-of select on separate lines in my code so is
> easier to read - do not want my data to go to a different line.
> 3) How do you edit value-of select lines to make a value a date, numeric,
> zero filled.
> 4)  How do I sort file after all records get written out to file in XSLT
> code
> 4)  Have to find a way to derive some of my fields that do not come
> directly from my XML file
>
>         how do I hardcode a value
>         how do a pull in a default value into a field such as current date
> in format yyyymmddhhmm or yyyymmdd
>         how do I set up a field from different fields (ie. my batch date
> format is as follows YYYYMMDDHHMMXCUR where X is a value from another field
> and CUR is a value from another field
>         how do I look for a partial value in a text field from XML file to
> set up a derived field - need to look for a certain string in
> invoice_number to set up PaymentTerms
>
> *Here is my CSV output from XML File (was able to extract all default
> fields)*
> 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*
>
>
> *XSLT Code*
> <?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:output 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" />,
> seormat 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" />,
> erived from part of invoice/invoice_number -->
>
>         <xsl:template match="/">
>                 <xsl:text>&#xa;</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>&#xa;</xsl:text>
>                   </xsl:for-each>
>         </xsl:template>
>
> </xsl:stylesheet>
> _____________________________________________________________________
> 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
>
>   XSL-List info and archive <http://www.mulberrytech.com/xsl/xsl-list>
> EasyUnsubscribe <-list/552232> (by
> email <>)


Current Thread
Keywords