XML转换为CSV [英] XML Convertion to CSV
问题描述
<？xml version =1.0encoding =UTF-8？>
< FirstTag version =1.0createTime =15:59:59DATE =20161209>
< SecondTag Name =House01>
< a>
< Furniture FURN_ID =FUR00001FURN_AMT =2price =10000/>
< Furniture FURN_ID =FUR00002FURN_AMT =1price =20000/>
< / a>
< b>
< Furniture FURN_ID =FUR00001FURN_AMT =2price =30000/>
< Furniture FURN_ID =FUR00003FURN_AMT =1price =40000/>
< / b>
< c>
< Furniture FURN_ID =FUR00002FURN_AMT =2price =50000/>
< Furniture FURN_ID =FUR00003FURN_AMT =1price =60000/>
< / c>
< d>
< Furniture FURN_ID =FUR00001FURN_AMT =1price =70000/>
< Furniture FURN_ID =FUR00002FURN_AMT =2price =80000/>
< / d>
< e>
< Furniture FURN_ID =FUR00002FURN_AMT =1price =90000/>
< Furniture FURN_ID =FUR00003FURN_AMT =2price =100000/>
< / e>
< f>
< Furniture FURN_ID =FUR00001FURN_AMT =1price =110000/>
< Furniture FURN_ID =FUR00002FURN_AMT =2price =120000/>
< Furniture FURN_ID =FUR00003FURN_AMT =2price =120000/>
< / f>
< / SecondTag>
< / FirstTag>
上面是我从Java程序生成的简单xml关键是，我想将此xml数据发送到另一个应用程序，其中已经有来自UI /批处理进程的csv加载函数。我听说过XSLT，但从来没有使用它，尝试了一些教程，但困惑的时间得到所有的值到csv。
这是什么应该看起来像在csv（开始，成功后需要做一些计算）：
在此示例中，在一个房子（HOUSE01）中，我想输出不同房间中的所有家具（即a是房间1，b是房间2，c是房间3等）。 / p>
我一直在尝试构建XSLT，下面是XSLT：
<？xml version =1.0encoding =utf-8？>
< xsl：stylesheet version =1.0xmlns：xsl =http://www.w3.org/1999/XSL/Transform>
< xsl：output method =text/>
< xsl：template match =/>
< xsl：text> Name，Furn_Id，a，b，c，d，e，f
< / xsl：text>
< xsl：variable name =delimiterselect ='，'/>
< xsl：for-each select =// SecondTag>
< xsl：variable name =hnameselect =@ Name/>
< xsl：for-each select =f / Furniture>
< xsl：value-of select ='$ hname'/>
< xsl：value-of select =$ delimiter/>
< xsl：variable name =var1select =@ FURN_ID/>
< xsl：variable name =varamt1select =@ FURN_AMT/>
< xsl：for-each select =// a / Furniture>
< xsl：variable name =var2select =@ FURN_ID/>
< xsl：variable name =varamt2select =@ FURN_AMT/>
< xsl：if test =$ var1 = $ var2>
< xsl：value-of select ='$ var2'/>
< xsl：value-of select =$ delimiter/>
< xsl：value -of select ='$ varamt2'/>
< xsl：value-of select =$ delimiter/>
< / xsl：if>
< / xsl：for-each>
< xsl：text>
< / xsl：text>
< / xsl：for-each>
< / xsl：for-each>
< / xsl：template>
< / xsl：stylesheet>
我使用了另一个页面的一些引用，可以构建一些简单的XSLT来将XML转换为CSV ，但是，我需要一些指导，以解决我的主要XML问题。
在将来，我可以得到节点值在循环内，我想
预期的最终csv结果：
Name，Furn_Id，a，b，c，d，e，f
House01，FUR00001,20000,60000，，70000，，110000
House01，FUR00002,20000，，100000， 160000,90000,240000
House01，FUR00003，，40000,60000，，200000,240000
谢谢。
这比它看起来更复杂，因为你想为当前房子中的每个不同 FURN_ID 创建一行（或至少从给定的示例看起来如此）。所以第一个任务是找到这些不同的 FURN_ID 值。
OTOH，如果每个房子都有完全相同的6房间，那么在其匹配的列标题下显示每个家具的第二个任务要简单得多：
XSLT 1.0
< xsl：stylesheet version =1.0
xmlns：xsl =http://www.w3.org/1999/XSL/ Transform>
< xsl：output method =textencoding =UTF-8/>
< xsl：key name =furnmatch =Furnitureuse =concat（@FURN_ID，'|'，../../@Name）/>
< xsl：template match =/ FirstTag>
< xsl：for-each select =SecondTag>
< xsl：variable name =columnsselect =*/>
< xsl：variable name =unique-rowsselect =* / Furniture [count（。| key（'furn'，concat（@FURN_ID，'|'，../../@Name ））[1]）= 1]/>
<！ - header - >
< xsl：text> Name，Furn_Id，a，b，c，d，e，f&＃10;< / xsl：text&
<！ - rows - >
< xsl：for-each select =$ unique-rows>
< xsl：value-of select =../../@ Name/>
< xsl：text>，< / xsl：text>
< xsl：value-of select =@ FURN_ID/>
< xsl：text>，< / xsl：text>
< xsl：variable name =idselect =@ FURN_ID/>
< xsl：for-each select =$ columns>
< xsl：variable name =cellselect =Furniture [@ FURN_ID = $ id]/>
< xsl：if test =$ cell>
< xsl：value-of select =$ cell / @ FURN_AMT * $ cell / @ price/>
< / xsl：if>
< xsl：if test =position（）！= last（）>
< xsl：text>，< / xsl：text>
< / xsl：if>
< / xsl：for-each>
< xsl：text>&＃10;< / xsl：text>
< / xsl：for-each>
< / xsl：for-each>
< / xsl：template>
< / xsl：stylesheet>
应用于您的输入示例，结果将是：
名称，Furn_Id，a，b，c，d，e，f
House01，FUR00001,20000,60000，，70000，，110000
House01，FUR00002,20000，，100000,160000,90000,240000
House01，FUR00003，，40000,60000，，200000,240000
这与您的预期输出略有不同，但我相信这是正确的。
<?xml version="1.0" encoding="UTF-8"?>
<FirstTag version="1.0" createTime="15:59:59" DATE="20161209">
<SecondTag Name="House01">
<a>
<Furniture FURN_ID="FUR00001" FURN_AMT="2" price="10000"/>
<Furniture FURN_ID="FUR00002" FURN_AMT="1" price="20000"/>
</a>
<b>
<Furniture FURN_ID="FUR00001" FURN_AMT="2" price="30000"/>
<Furniture FURN_ID="FUR00003" FURN_AMT="1" price="40000"/>
</b>
<c>
<Furniture FURN_ID="FUR00002" FURN_AMT="2" price="50000"/>
<Furniture FURN_ID="FUR00003" FURN_AMT="1" price="60000"/>
</c>
<d>
<Furniture FURN_ID="FUR00001" FURN_AMT="1" price="70000"/>
<Furniture FURN_ID="FUR00002" FURN_AMT="2" price="80000"/>
</d>
<e>
<Furniture FURN_ID="FUR00002" FURN_AMT="1" price="90000"/>
<Furniture FURN_ID="FUR00003" FURN_AMT="2" price="100000"/>
</e>
<f>
<Furniture FURN_ID="FUR00001" FURN_AMT="1" price="110000"/>
<Furniture FURN_ID="FUR00002" FURN_AMT="2" price="120000"/>
<Furniture FURN_ID="FUR00003" FURN_AMT="2" price="120000"/>
</f>
</SecondTag>
</FirstTag>
Above is the simple xml (with node value), that I produced from my Java program. The point is, I want to send this xml data to another application, where there's already a csv load function from the UI/batch processes. I've heard of XSLT but never use of it, tried some of the tutorial but got confused in the time to get all the values into a csv.
Here's what it should look like in csv (to start, after success need to do some calculation):
In this example in one house (HOUSE01) I would like to output all the furniture in different room (i.e. a is room 1, b is room 2, c is room 3, etc).
I've been trying to build the XSLT, below is the XSLT:
<?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" />
<xsl:template match="/">
<xsl:text>Name,Furn_Id,a,b,c,d,e,f
</xsl:text>
<xsl:variable name="delimiter" select="','" />
<xsl:for-each select="//SecondTag">
<xsl:variable name="hname" select="@Name"/>
<xsl:for-each select="f/Furniture">
<xsl:value-of select='$hname' />
<xsl:value-of select="$delimiter"/>
<xsl:variable name="var1" select="@FURN_ID"/>
<xsl:variable name="varamt1" select="@FURN_AMT"/>
<xsl:for-each select="//a/Furniture">
<xsl:variable name="var2" select="@FURN_ID"/>
<xsl:variable name="varamt2" select="@FURN_AMT"/>
<xsl:if test="$var1 = $var2">
<xsl:value-of select='$var2' />
<xsl:value-of select="$delimiter"/>
<xsl:value-of select='$varamt2' />
<xsl:value-of select="$delimiter"/>
</xsl:if>
</xsl:for-each>
<xsl:text>
</xsl:text>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
I'm using some reference from another page, and can build some simple XSLT to transform XML to CSV, however, I need some guidance in order to solve my main XML issue.
In the future after I can get the node value inside the loop, I'd like to sum the total price of every furniture for each room.
Expected final csv result:
Name,Furn_Id,a,b,c,d,e,f
House01,FUR00001,20000,60000,,70000,,110000
House01,FUR00002,20000,,100000,160000,90000,240000
House01,FUR00003,,40000,60000,,200000,240000
Thank you.
Getting the value of an attribute in XML
This is more complicated than it may seem, because you want a row for each distinct FURN_ID in the current house (or at least so it seems from the given example). So the first task is to find these distinct FURN_ID values.
OTOH, if each house has exactly the same 6 rooms, then the second task of showing each furniture under its matching column header is much simpler:
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:key name="furn" match="Furniture" use="concat(@FURN_ID, '|', ../../@Name)" />
<xsl:template match="/FirstTag">
<xsl:for-each select="SecondTag">
<xsl:variable name="columns" select="*" />
<xsl:variable name="unique-rows" select="*/Furniture[count(. | key('furn', concat(@FURN_ID, '|', ../../@Name))[1]) = 1]"/>
<!-- header -->
<xsl:text>Name,Furn_Id,a,b,c,d,e,f </xsl:text>
<!-- rows -->
<xsl:for-each select="$unique-rows">
<xsl:value-of select="../../@Name"/>
<xsl:text>,</xsl:text>
<xsl:value-of select="@FURN_ID"/>
<xsl:text>,</xsl:text>
<xsl:variable name="id" select="@FURN_ID" />
<xsl:for-each select="$columns">
<xsl:variable name="cell" select="Furniture[@FURN_ID=$id]" />
<xsl:if test="$cell">
<xsl:value-of select="$cell/@FURN_AMT * $cell/@price"/>
</xsl:if>
<xsl:if test="position()!=last()">
<xsl:text>,</xsl:text>
</xsl:if>
</xsl:for-each>
<xsl:text> </xsl:text>
</xsl:for-each>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Applied to your input example, the result will be:
Name,Furn_Id,a,b,c,d,e,f
House01,FUR00001,20000,60000,,70000,,110000
House01,FUR00002,20000,,100000,160000,90000,240000
House01,FUR00003,,40000,60000,,200000,240000
which is slightly different from your expected output, but I believe it is the correct one.
这篇关于XML转换为CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
