Reputation: 13
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 </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
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 </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="'
' />
</xsl:for-each>
</xsl:for-each>
</xsl:template>
Output a single value per line of XSLT to keep things readable and tidy.
Upvotes: 0
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