XML to Fixed width text file formatted details

Question

I have an XML like this

<?xml version="1.0" encoding="UTF-8"?>
<Report>
  <table1>
    <Detail_Collection>
      <Detail>
        <ReceiptNo>RN12345678</ReceiptNo>
        <ReceiptDate>1980/11/11</ReceiptDate>
        <LastName>Dela Cruz</LastName>
        <FirstName>Juan</FirstName>
        <PurchaseDetails>
          <Item>Wood</Item>
          <Price>25.65</Price>
          <Quantity>2</Quantity>
        </PurchaseDetails>
        <PurchaseDetails>
          <Item>Axe</Item>
          <Price>50.56</Price>
          <Quantity>5</Quantity>
        </PurchaseDetails>
      </Detail>
    </Detail_Collection>
  </table1>
</Report>

and I need to convert it to a flat text file using XSLT 1.0

I found this nice solution

<?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"/>

    <xsl:variable name="some_spaces" select="'                                                                  '" />

    <xsl:template match="/">
        <xsl:apply-templates select="//Detail_Collection/Detail" />
    </xsl:template>

    <xsl:template match="Detail_Collection/Detail">
        <xsl:apply-templates mode="format" select="SSN">
            <xsl:with-param name="width" select="number(9-1)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format_date" select="DOB">
            <xsl:with-param name="width" select="number(17-10)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="LastName">
            <xsl:with-param name="width" select="number(33-18)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="FirstName">
            <xsl:with-param name="width" select="number(46-34)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format_date" select="Date">
            <xsl:with-param name="width" select="number(54-47)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="Time">
            <xsl:with-param name="width" select="number(62-55)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentStreetAddress1">
            <xsl:with-param name="width" select="number(90-63)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentCity">
            <xsl:with-param name="width" select="number(115-91)"/>
        </xsl:apply-templates>
        <xsl:apply-templates mode="format" select="CurrentState">
            <xsl:with-param name="width" select="number(131-116)"/>
        </xsl:apply-templates>
        <xsl:text>&#10;</xsl:text>
    </xsl:template>

    <xsl:template  match="node()" mode ="format">
        <xsl:param name="width" />
        <xsl:value-of select="substring(concat(text(),$some_spaces ), 1, $width+1)"/>
    </xsl:template>
    <xsl:template  match="node()" mode="format_date">
        <xsl:param name="width" />
        <xsl:value-of select="substring(concat(translate(text(),'/',''),$some_spaces ), 1, $width+1)"/>
    </xsl:template>

</xsl:stylesheet>

But the problem is that I have to format every detail according to its data type like below

Alphanumeric - should be 30 characters right filled with spaces

Numeric (Unsigned) - should be 15 characters left filled with zeroes e.g. 000000000012345

Numeric (signed) - should be 15 characters left filled with zeroes and if negative should denote 'N' e.g. N00000000012345

From my XML file the output should be:

RN12345678                   19801111Dela Cruz                    Juan               Wood               000000000002565000000000000002
RN12345678                   19801111Dela Cruz                    Juan               Axe                000000000005056000000000000005

and for example the prices are negative then

RN12345678                   19801111Dela Cruz                    Juan               Wood               N00000000002565000000000000002
RN12345678                   19801111Dela Cruz                    Juan               Axe                N00000000005056000000000000005

and by the way i have some fields that have 300 characters (like a filler) so i dont know if I need to put 300+ spaces in the variable some_spaces

Dates should be 8 characters YYYYMMDD.

I have a template which im using but not sure how to put the 'N' for the negative ones and how to format the dates according to the requirement.

Here is the template:

<xsl:template name="prepend-pad">
    <!-- recursive template to right justify and prepend the value with whatever padChar is passed in   -->
    <xsl:param name="padChar" />
    <xsl:param name="padVar" />
    <xsl:param name="length" />
    <xsl:choose>
      <xsl:when test="string-length($padVar) &lt; $length">
        <xsl:call-template name="prepend-pad">
          <xsl:with-param name="padChar" select="$padChar"/>
          <xsl:with-param name="padVar" select="concat($padChar,$padVar)"/>
          <xsl:with-param name="length" select="$length"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="substring($padVar,string-length($padVar) - $length + 1)" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

Thanks

XSL version could be important here, e.g. access to [castable as](http://www.xml.com/pub/a/2003/10/01/tr.html) — StuartLC, Dec 31 '14 at 05:10
Could you post the expected result of your example? The XSLT does not match your XML, so it's unclear which details you want to output. -- Also, you have no format specified for dates. — michael.hor257k, Dec 31 '14 at 05:25
Regarding the padding, see http://stackoverflow.com/questions/7808421/xsl-left-right-justification-with-padding — Kokkie, Dec 31 '14 at 12:03
Hi Michael, edited my post to include the expected result, thanks — zyberjock, Jan 06 '15 at 10:09

michael.hor257k · Accepted Answer · 2015-01-07T11:25:49.027

How about:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8"/>

<xsl:variable name="spaces" select="'                              '"/>

<xsl:template match="/">
    <xsl:for-each select="Report/table1/Detail_Collection/Detail/PurchaseDetails">
        <xsl:apply-templates select="../ReceiptNo"/>
        <xsl:apply-templates select="../ReceiptDate"/>
        <xsl:apply-templates select="../LastName"/>
        <xsl:apply-templates select="../FirstName"/>
        <xsl:apply-templates select="Item"/>
        <xsl:call-template name="format-number">
            <xsl:with-param name="number" select="100 * Price"/>
        </xsl:call-template>
        <xsl:call-template name="format-number">
            <xsl:with-param name="number" select="Quantity"/>
        </xsl:call-template>
        <xsl:if test="position()!=last()">
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
    </xsl:for-each>     
</xsl:template>

<xsl:template match="ReceiptNo | LastName | FirstName | Item">
    <xsl:value-of select="substring(concat(., $spaces), 1, 30)"/>
</xsl:template>

<xsl:template match="ReceiptDate">
    <xsl:value-of select="translate(., '/', '')"/>
</xsl:template>

<xsl:template name="format-number">
    <xsl:param name="number" select="0"/>
    <xsl:choose>
        <xsl:when test="$number >= 0">
            <xsl:value-of select="format-number($number, '000000000000000')"/>
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="format-number(-$number, 'N00000000000000')"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

</xsl:stylesheet>

I am afraid I did not understand this part:

and by the way i have some fields that have 300 characters (like a filler) so i dont know if I need to put 300+ spaces in the variable some_spaces

Edit:

To insert 300 spaces in the resulting line, I suggest you use simply:

<xsl:text>  (300 spaces here)  </xsl:text>

It's possible to use a named template to generate any amount of spaces dynamically but since you need a constant number, I can't see any advantage to it.

And regarding the date it will be given to me as MM/dd/yyyy and i need to format it as yyyyMMdd, sorry for the wrong sample data I provided.

If so, change the template matching the date field to:

<xsl:template match="ReceiptDate">
    <xsl:value-of select="concat(substring(., 7, 4), substring(., 1, 2), substring(., 4, 2))"/>
</xsl:template>

Hi Michael, what I mean by that is that my file have a field which is 300 characters and should be filled by blanks, its like a reserved field and its somewhere before the end of the line. And regarding the date it will be given to me as MM/dd/yyyy and i need to format it as yyyyMMdd, sorry for the wrong sample data I provided. — zyberjock, Jan 07 '15 at 02:26
I am sorry, but I still don't follow. Do you mean you need to add 300 spaces to each line **of the output** (regardless of the input)? --- BTW, which processor are you using? You may be helped by some extension functions here, if your processor supports them. — michael.hor257k, Jan 07 '15 at 06:20
yes something like that, its like a reserved field, i am just using VS2013. — zyberjock, Jan 07 '15 at 10:53

score 0 · Answer 2 · answered Dec 31 '14 at 17:18

As mentioned in the comments, your XSL has very little to do with the XML you provided. So, working from the vague requirements for a flat text file and formatting the data with padding I made some guesses and came up with the following.

<?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"/>

    <xsl:variable name="some_spaces" select="'                                                                  '" />

    <xsl:template match="/">
        <xsl:apply-templates select="//Detail_Collection/Detail" />
    </xsl:template>

    <xsl:template match="Detail_Collection/Detail">
        <xsl:for-each select="PurchaseDetails">
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../LastName"/>
                <xsl:with-param name="width" select="number(33-18)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../FirstName"/>
                <xsl:with-param name="width" select="number(46-34)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="../ReceiptDate"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Item"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('A')"/>
            </xsl:call-template>
            <xsl:text>,</xsl:text>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Quantity"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('N')"/>
            </xsl:call-template>
            <xsl:call-template name="format">
                <xsl:with-param name="textnode" select="Price"/>
                <xsl:with-param name="width" select="number(54-45)"/>
                <xsl:with-param name="type" select="string('N')"/>
            </xsl:call-template>
            <xsl:text>&#10;</xsl:text>
        </xsl:for-each>
    </xsl:template>

    <xsl:template name="format">
        <xsl:param name="width"/>
        <xsl:param name="textnode"/>
        <xsl:param name="type"/>
        <xsl:variable name="leader_padding">
            <xsl:choose>
                <xsl:when test="$type='N' or $type='S'">
                    <xsl:value-of select="translate($some_spaces,' ','0')"/>
                </xsl:when>
                <xsl:otherwise>
                    <xsl:value-of select="$some_spaces"/>
                </xsl:otherwise>
            </xsl:choose>
        </xsl:variable>

        <xsl:variable name="bigstring" select="concat($leader_padding,$textnode)"/>
        <xsl:variable name="truncatedstring" select="substring($bigstring,string-length($bigstring)-$width)"/>

        <xsl:choose>
            <xsl:when test="$type='A'">
                <xsl:text> </xsl:text>
            </xsl:when>
            <xsl:when test="$type='N' or ($type='S' and number($textnode >= 0))">
                <xsl:text>0</xsl:text>
            </xsl:when>
            <xsl:otherwise>
                <!-- type must be S and value must be negative -->
                <xsl:text>N</xsl:text>
            </xsl:otherwise>
        </xsl:choose>
        <xsl:value-of select="$truncatedstring"/>

    </xsl:template>
</xsl:stylesheet>

Which when run against your input xml file produces:

    Dela Cruz,          Juan, 1980/11/11,       Wood,0000000000200000025.65
    Dela Cruz,          Juan, 1980/11/11,        Axe,0000000000500000050.56

There are two lines here because the input format isn't really flat. So I opted to go with one line per purchased item.

XML to Fixed width text file formatted details

2 Answers2

Edit: