CODEWITHSUNDEEP
xmlcsvxsltxslt-1.0
Eliam
Eliam

Reputation: 13

Convert XML to CSV with XSLT

I'd like to convert XML into CSV using an XSLT.Could please someone help me to rearrange the XSL file so that each data, if it exists, is correctly stored in the columns according to the header. Otherwise the column must remain empty.

My inputs are

XML file:

<?xml version="1.0" encoding="ISO-8859-1"?>
<message version="2.0" system="avs/3"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <header>
        <creation-date>2016-10-06</creation-date>
        <creation-time>10:46:00</creation-time>
    </header>
    <body>
        <subscription>
            <promotion-source>5011158916</promotion-source>
            <publication>00069329</publication>
            <business-partner id="" role-type="AG">
                <company1>AM</company1>
                <company2>Gebug</company2>
                <company3></company3>
                <communication>
                    <communication-type>T</communication-type>
                    <communication-value></communication-value>
                </communication>
                <communication>
                    <communication-type>F</communication-type>
                    <communication-value></communication-value>
                </communication>
                <communication>
                    <communication-type>WW</communication-type>
                    <communication-value>mm@hvhv.de</communication-value>
                </communication>
            </business-partner>
            <business-partner id="Doc" role-type="WE">
                <last-name1>M</last-name1>
                <first-name>A</first-name>
                <street>str.</street>
                <zipcode>33</zipcode>
                <company1>PM</company1>
                <company2>Geung</company2>
                <company3></company3>
                <communication>
                    <communication-type>T</communication-type>
                    <communication-value>0883</communication-value>
                </communication>
                <communication>
                    <communication-type>F</communication-type>
                    <communication-value>608</communication-value>
                </communication>
                <communication>
                    <communication-type>WW</communication-type>
                    <communication-value>BIN@online.de</communication-value>
                </communication>
                <attribute>
                    <attribute-type>EMNL</attribute-type>
                    <attribute-value>N</attribute-value>
                </attribute>
            </business-partner>
        </subscription>
        <subscription>
            <promotion-source>916</promotion-source>
            <publication>329</publication>
            <business-partner id="Mas" role-type="AG">
                <company1>iß</company1>
                <company2>mp; Co.</company2>
                <company3></company3>
            </business-partner>
            <business-partner id="" role-type="WE">
                <last-name1>ar</last-name1>
                <first-name>ro</first-name>
                <street>str</street>
                <zipcode>858</zipcode>
                <company1>mp;iß</company1>
                <company2>mp;G</company2>
                <company3></company3>
                <communication>
                    <communication-type>T</communication-type>
                    <communication-value></communication-value>
                </communication>
                <communication>
                    <communication-type>F</communication-type>
                    <communication-value></communication-value>
                </communication>
                <communication>
                    <communication-type>WW</communication-type>
                    <communication-value>p@m.de</communication-value>
                </communication>
                <attribute>
                    <attribute-type>L</attribute-type>
                    <attribute-value>N</attribute-value>
                </attribute>
            </business-partner>
        </subscription>
        <subscription>
            <promotion-source>916</promotion-source>
            <publication>009</publication>
            <business-partner id="Mas" role-type="AG">
                <company1>ten</company1>
                <company2>GR</company2>
                <company3></company3>
                <communication>
                    <communication-type>T</communication-type>
                    <communication-value>061</communication-value>
                </communication>
                <communication>
                    <communication-type>F</communication-type>
                    <communication-value></communication-value>
                </communication>
                <communication>
                    <communication-type>WW</communication-type>
                    <communication-value>alff@a-h-architekten.de</communication-value>
                </communication>
            </business-partner>
            <business-partner id="Doc" role-type="WE">
                <last-name1>zek</last-name1>
                <first-name>Ht</first-name>
                <street>Pt</street>
                <zipcode>62</zipcode>
                <company1>ten</company1>
                <company2>GR</company2>
                <company3></company3>
                <attribute>
                    <attribute-type>L</attribute-type>
                    <attribute-value>N</attribute-value>
                </attribute>
            </business-partner>
        </subscription>
    </body>
</message>

And here is the xsl stylesheet file:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="UTF-8" />
<xsl:template match="/">
    <!-- Output the CSV header -->
    <xsl:text>promotion-source;publication;BP-id;BP-role-type;AG-last-name;AG-first-name;AG-company1;AG-company2;AG-company3;AG-tel;AG-fax;AG-email;BP-id;BP-role-type;WE-last-name;WE-first-name;WE-street;WE-zipcode;WE-company1;WE-company2;WE-company3;WE-tel;WE-fax;WE-email;attribute-type;attribute-value&#10;</xsl:text>

    <!-- Output the value -->
    <xsl:for-each select="message/body/subscription">
        <!-- begin value -->
        <xsl:value-of select="concat(promotion-source, ';', publication,';')" />
        <!-- business-partner values -->
        <xsl:for-each select="business-partner">
            <xsl:value-of select="@id" />
            <xsl:text>;</xsl:text>
            <xsl:value-of select="@role-type" />
            <xsl:text>;</xsl:text>
            <xsl:value-of
                select="concat(last-name1, ';', first-name, ';', street, ';', zipcode, ';', company1, ';', company2, ';', company3, ';')" />
            <xsl:text>;</xsl:text>

            <xsl:for-each select="communication">
                <xsl:value-of select="communication-value" />
            </xsl:for-each>

            <xsl:for-each select="attribute">
                <xsl:value-of select="attribute-type" />
                <xsl:text>;</xsl:text>
                <xsl:value-of select="attribute-value" />
            </xsl:for-each>
        </xsl:for-each>
        <xsl:text>
</xsl:text>
    </xsl:for-each>
</xsl:template>

I have all the wanted data in the Output file except that they are not stored in their respective columns and that is my problem.Your help is appreciated. :)

Thank you in advance.

Upvotes: 0

Views: 2922

Answers (2)

Tomalak
Tomalak

Reputation: 338396

You want a flat list as output, therefore you can't use nested structures (for-each within for-each) to create it.

You seem to want to list business partners and their details, so your single loop should be focused on <business-partner> elements, and all the details should be picked relative to them.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="text" encoding="UTF-8" />
  <xsl:template match="/">
    <!-- Output the CSV header -->
    <xsl:text>promotion-source;publication;BP-id;BP-role-type;AG-last-name;AG-first-name;AG-company1;AG-company2;AG-company3;AG-tel;AG-fax;AG-email;BP-id;BP-role-type;WE-last-name;WE-first-name;WE-street;WE-zipcode;WE-company1;WE-company2;WE-company3;WE-tel;WE-fax;WE-email;attribute-type;attribute-value&#10;</xsl:text>

    <!-- Output the values -->
    <xsl:for-each select="//business-partner">
      <xsl:value-of select="concat(../promotion-source, ';')">
      <xsl:value-of select="concat(../publication, ';')">
      <xsl:value-of select="concat(@id, ';')">
      <xsl:value-of select="concat(@role-type, ';')" />
      <!-- and so on -->

      <xsl:value-of select="concat(communication[communication-type = 'T']/communication-value, ';')" />
      <!-- and so on -->

      <xsl:value-of select="concat(attribute[attribute-type = 'EMNL']/attribute-value, ';')" />
      <!-- and so on -->

      <xsl:value-of select="'&#xA;' />
    </xsl:for-each>
  </xsl:for-each>
</xsl:template>

Output a single value per line of XSLT to keep things readable and tidy.

Upvotes: 0

Jesus Peralta
Jesus Peralta

Reputation: 681

The number of columns are differents for business-partner AG and for business-partner WE and you are using the same for each to concatenate these columns, that`s the reason why your columns doesn't match

Upvotes: 1

Related Questions