Convert XML having multiple for-each case to CSV
问题
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 the CSV.
XML file that I am trying to convert it 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 XSL file that it not working 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,'

')"/>
<xsl:for-each select="/TURFS/TURF">
<xsl:value-of select="concat(AREANAME,',',DEAFAULT,',',ALTERNATE,',

')"/>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Currently while converting it is printing row TECHNICIANID and CUID only. Other 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
解答
这个问题需要从包含多级父子节点的xml中提取数据导出csv文件。Java 实现则代码较长。
用Java 下的开源包 SPL 很容易写，只要几句：
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提供了JDBC 供 JAVA 调用，把上面的脚本存为 xml2csv.splx，在 JAVA 中以存储过程的方式调用脚本文件：
…
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");
st=con.prepareCall("call xml2csv()");
st.execute();
…
English version