Question

Source: https://stackoverflow.com/questions/68605295/convert-xml-having-multiple-for-each-case-to-csv

I have written a Java Program that converts XML to CSV. But currently it is converting partial only.

FileUtils.writeByteArrayToFile(new File("src/main/resources/excel/Data.xml"), inputFile);

File stylesheet = new File("src/main/resources/excel/Data.xsl");

File xmlSource = new File("src/main/resources/excel/Data.xml");

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

Document document = builder.parse(xmlSource);

StreamSource stylesource = new StreamSource(stylesheet);

Transformer transformer = TransformerFactory.newInstance().newTransformer(stylesource);

Source source = new DOMSource(document);

String path = "src/main/resources/excel/validatedXmlToCSV.csv";

Result outputTarget = new StreamResult(new File(path));

transformer.transform(source, outputTarget);

This is the Java code that takes XML and XSL and converts it to CSV.

The XML file that I am trying to convert to CSV is:

<root>

<row>

<TECHNICIANID>AA5263</TECHNICIANID>

<CUID>AA5263</CUID>

<TURFS>

<TURF>

<AREANAME>CA_MILPITAS_ABEL_A</AREANAME>

<DEFAULT>Y</DEFAULT>

<ALTERNATE>Y</ALTERNATE>

</TURF>

<TURF>

<AREANAME>CA_SNJS_WHITE_RD_A</AREANAME>

<DEFAULT>Y</DEFAULT>

<ALTERNATE>Y</ALTERNATE>

</TURF>

</TURFS>

</row>

<row>

<TECHNICIANID>AC1964</TECHNICIANID>

<CUID>AC1964</CUID>

<TURFS>

<TURF>

<AREANAME>CA_MILPITAS_ABEL_A</AREANAME>

<DEFAULT>Y</DEFAULT>

<ALTERNATE>Y</ALTERNATE>

</TURF>

<TURF>

<AREANAME>CA_SNJS_WHITE_RD_A</AREANAME>

<DEFAULT>Y</DEFAULT>

<ALTERNATE>Y</ALTERNATE>

</TURF>

</TURFS>

</row>

</root>

Let me show you how I wrote the XML file that it does not properly:

<?xml version="1.0"?>

<xsl:stylesheet version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

xmlns:fo="http://www.w3.org/1999/XSL/Format" >

<xsl:output method="text" omit-xml-declaration="yes" indent="no"/>

<xsl:template match="/">TECHNICIANID,CUID,TURFS/TURF/0/AREANAME,TURFS/TURF/0/DEFAULT,TURFS/TURF/0/ALTERNATE,TURFS/TURF/1/AREANAME,TURFS/TURF/1/DEFAULT,TURFS/TURF/1/ALTERNATE

<xsl:for-each select="/root/row">

<xsl:value-of select="concat(TECHNICIANID,',',CUID,'

&#xA;')"/>

<xsl:for-each select="/TURFS/TURF">

<xsl:value-of select="concat(AREANAME,',',DEAFAULT,',',ALTERNATE,',

&#xA;')"/>

</xsl:for-each>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Currently at conversion it is printing row TECHNICIANID and CUID only. Others it is not printing.

Expected Output:

TECHNICIANID,CUID,TURFS/TURF/0/AREANAME,TURFS/TURF/0/DEFAULT,TURFS/TURF/0/ALTERNATE,TURFS/TURF/1/AREANAME,TURFS/TURF/1/DEFAULT,TURFS/TURF/1/ALTERNATE

AA5263,AA5263,CA_MILPITAS_ABEL_A,Y,Y,CA_SNJS_WHITE_RD_A,Y,Y

AC1964,AC1964,CA_MILPITAS_ABEL_A,Y,Y,CA_SNJS_WHITE_RD_A,Y,Y

Answer

Your problem involves extracting data from a multilevel XML file and convert it to a CSV file. The Java code for this is complicated.

It is convenient to perform the conversion using SPL, the open-source Java package. You just need several lines of code:

	A
1	=xml(file("Data.xml").read(),"root/row").conj(TECHNICIANID|CUID|TURFS.TURF.conj(~.array()))
2	=create(TECHNICIANID,CUID,TURFS/TURF/0/AREANAME,TURFS/TURF/0/DEFAULT,TURFS/TURF/0/ALTERNATE,TURFS/TURF/1/AREANAME,TURFS/TURF/1/DEFAULT,TURFS/TURF/1/ALTERNATE).record(A1)
3	=file("validatedXmlToCSV.csv").export@ct(A2)

 

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as xml2csv.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st=con.prepareCall("call xml2csv()");

st.execute();

…

View SPL source code.