CODEWITHSUNDEEP
xmlxslt
Gagan
Gagan

Reputation: 1

XML to flat file conversion using XSLT

I need to convert the following XML to a flatfile with delimiter as "|", using XSLT.

This is my XML:

<?xml version="1.0" encoding="UTF-8" standalone="no" ?><GeneralLedgerReport targetNamespace="http://www.portal.com/schemas/GLSync" xmlns="http://www.portal.com/schemas/GLSync" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.portal.com/schemas/GLSync brm_gl_data.xsd">
        <SourceSystemID>Germany</SourceSystemID>
        <ReportID>0.0.0.1-697031-1136</ReportID>
        <RevenueType>Unbilled earned</RevenueType>
        <BRM_GL_Segment>.</BRM_GL_Segment>
        <ReportCreatedTime>
                <Year>2013</Year>
                <Month>01</Month>
                <Day>14</Day>
                <Hours>16</Hours>
                <Minutes>2</Minutes>
                <Seconds>3</Seconds>
        </ReportCreatedTime>
        <PeriodStartTime>
                <Year>2012</Year>
                <Month>10</Month>
                <Day>15</Day>
                <Hours>0</Hours>
                <Minutes>0</Minutes>
                <Seconds>0</Seconds>
        </PeriodStartTime>
        <PeriodEndTime>
                <Year>2012</Year>
                <Month>10</Month>
                <Day>16</Day>
                <Hours>0</Hours>
                <Minutes>0</Minutes>
                <Seconds>0</Seconds>
        </PeriodEndTime>
</GeneralLedgerReport>

Required Output: General Ledger Report

Germany|0.0.0.1-697031-1136|Unbilled earned|2013/01/14-16:02:03|2012/10/15-00:00:00 Germany|0.0.0.1-697031-1136|Unbilled earned|2013/01/14-16:02:03|2012/10/15-00:00:00 Germany|0.0.0.1-697031-1136|Unbilled earned|2013/01/14-16:02:03|2012/10/15-00:00:00 Germany|0.0.0.1-697031-1136|Unbilled earned|2013/01/14-16:02:03|2012/10/15-00:00:00

I tried couple of things but the output comes as:

Germany0.0.0.1-697031-1136Unbilled earned20130114160203201210150

Need this urgently, please help! Thanks!

I am new to XSLT, not sure how to get the elements separately with delimiters, I tried this:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exslt="http://exslt.org/common"
        xmlns:java="http://xml.apache.org/xalan/java" exclude-result-prefixes="exslt java">
<xsl:output method="text" version="1.0" encoding="ISO-8859-1" indent="yes"/>

        <xsl:template match="/">
          <xsl:apply-templates select="/"/>
        </xsl:template>
        <xsl:template match="/">
           <xsl:value-of  select="."/>
        </xsl:template>

</xsl:stylesheet>

More XML:

    <SourceSystemID>Germany</SourceSystemID>
    <ReportID>0.0.0.1-697031-1116</ReportID>
    <RevenueType>Unbilled earned</RevenueType>
    <BRM_GL_Segment>.</BRM_GL_Segment>
    <ReportCreatedTime>
            <Year>2013</Year>
            <Month>01</Month>
            <Day>14</Day>
            <Hours>16</Hours>
            <Minutes>1</Minutes>
            <Seconds>59</Seconds>
    </ReportCreatedTime>
    <PeriodStartTime>
            <Year>2012</Year>
            <Month>09</Month>
            <Day>25</Day>
            <Hours>0</Hours>
            <Minutes>0</Minutes>
            <Seconds>0</Seconds>
    </PeriodStartTime>
    <PeriodEndTime>
            <Year>2012</Year>
            <Month>09</Month>
            <Day>26</Day>
            <Hours>0</Hours>
            <Minutes>0</Minutes>
            <Seconds>0</Seconds>
    </PeriodEndTime>
    <SourceSystemID>Germany</SourceSystemID>
    <ReportID>0.0.0.1-697031-1136</ReportID>
    <RevenueType>Unbilled earned</RevenueType>
    <BRM_GL_Segment>.</BRM_GL_Segment>
    <ReportCreatedTime>
            <Year>2013</Year>
            <Month>01</Month>
            <Day>14</Day>
            <Hours>16</Hours>
            <Minutes>2</Minutes>
            <Seconds>3</Seconds>
    </ReportCreatedTime>
    <PeriodStartTime>
            <Year>2012</Year>
            <Month>10</Month>
            <Day>15</Day>
            <Hours>0</Hours>
            <Minutes>0</Minutes>
            <Seconds>0</Seconds>
    </PeriodStartTime>
    <PeriodEndTime>
            <Year>2012</Year>
            <Month>10</Month>
            <Day>16</Day>
            <Hours>0</Hours>
            <Minutes>0</Minutes>
            <Seconds>0</Seconds>
    </PeriodEndTime>

Upvotes: 0

Views: 5060

Answers (2)

MarcoS
MarcoS

Reputation: 13574

You can get some inspiration from the following XSLT:

<?xml version='1.0' encoding='utf-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:glsync="http://www.portal.com/schemas/GLSync" version="1.0">

    <xsl:output method="text" />

    <xsl:template match="glsync:GeneralLedgerReport">
        <xsl:value-of select="./glsync:SourceSystemID" />
        <xsl:text>|</xsl:text>
        <xsl:value-of select="./glsync:ReportID" />
        <xsl:text>|</xsl:text>
        <xsl:value-of select="./glsync:RevenueType" />
        <xsl:text>|</xsl:text>
        <xsl:apply-templates select="./glsync:ReportCreatedTime" />
        <xsl:text>|</xsl:text>
        <xsl:apply-templates select="./glsync:PeriodStartTime" />
    </xsl:template>

    <xsl:template match="glsync:ReportCreatedTime">
        <xsl:value-of
            select="concat(
                glsync:Year, '/', 
                glsync:Month, '/', 
                glsync:Day, '-', 
                format-number(glsync:Hours, '00'), ':', 
                format-number(glsync:Minutes, '00'), ':', 
                format-number(glsync:Seconds,  '00'))" />
    </xsl:template>

    <xsl:template match="glsync:PeriodStartTime">
        <xsl:value-of
            select="concat(
                glsync:Year, '/', 
                glsync:Month, '/', 
                glsync:Day, '-', 
                format-number(glsync:Hours, '00'), ':', 
                format-number(glsync:Minutes, '00'), ':', 
                format-number(glsync:Seconds,  '00'))" />
    </xsl:template>

</xsl:stylesheet>

Some notes:

  • you must use namespace in matching elements, because your XML has a targetNamespace;

  • you could use the concat function also in the template matching glsync:GeneralLedgerReport

  • note the use of the format-number function to get hours, minutes and seconds on two digits

  • finally, your longer XML seems strange because all GeneralLedgerReport, ReportCreatedTime, PeriodStartTime and PeriodEndTime tags are on the same level; perhaps you have some tag enclosing them so that you have nested structures for every GeneralLedgerReport

I hope this helps.

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52675

Try looking at this question but use a | instead of a comma

XSLT Concatenating the values with comma (,)

Upvotes: 1

Related Questions