Percival_a
Reputation: 53
Trying to convert XML to CSV using XSL
I have XML data that i need to convert into a CSV format. I use an application to generate the xml from a design program. Here i specify variables which i would like to be included in the xml by a custom XML file
<?xml version="1.0" encoding="windows-1250"?>
<VarList active="1">
<Var exclude="0" ID="0" Name="V_Width"/>
<Var exclude="0" ID="2" Name="V_Depth"/>
<Var exclude="0" ID="1" Name="V_Height"/>
</VarList>
The exported XML data that is provided based on my custom XML, appears as below
<?xml version="1.0" encoding="utf-16"?>
<Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="Project.xsd" Name="Job">
<Version>1</Version>
<ContentID>1</ContentID>
<Source>
<Name>
<![CDATA[2020 Design]]></Name>
<BuildVersion>
<![CDATA[V: 8.1.3072, Build_81_3072]]></BuildVersion>
<ComputerName>L61795</ComputerName>
</Source>
<SendSize>000000000</SendSize>
<Priority>N</Priority>
<Design>
<CheckSum>1092308776</CheckSum>
<Document>
<DocType>kitchen</DocType>
<FilePath>
<![CDATA[C:\Users\percival_a\Documents\2020 Files\D001_20120323_5.kit]]></FilePath>
<FileName>
<![CDATA[D001_20120323_5]]></FileName>
</Document>
<JobInfo>
<FileName>
<![CDATA[C:\Users\percival_a\Documents\2020 Files\D001_20120323_5.kit]]></FileName>
</JobInfo>
<Catalog>
<Identification>
<Name>KITMANIA</Name>
<Description>
<![CDATA[Kitchen Mania Catalogue]]></Description>
<CurrencyName>USD</CurrencyName>
<CreationDate>
<![CDATA[2011/09/20 00:00:00]]></CreationDate>
<ModificationDate>
<![CDATA[2012/02/20 14:42:26]]></ModificationDate>
<MeasurementUnit>Metric</MeasurementUnit>
<Profile>
<Name>
<![CDATA[Kitchen Mania]]></Name>
</Profile>
<CatalogHasStyle>0</CatalogHasStyle>
</Identification>
<Material ID="IDM-1-2605-95005">
<Name>White</Name>
<Grain>N</Grain>
<Thickness>0</Thickness>
</Material>
<Material ID="IDM-1-2605-8045">
<Name>
<![CDATA[Metal - Stainless Steel]]></Name>
<Grain>N</Grain>
<Thickness>0</Thickness>
</Material>
<FeatureSet ID="IDFS-1" Type="Style">
<Code>
<![CDATA[1]]></Code>
<Description>
<![CDATA[Price Level 1]]></Description>
</FeatureSet>
<Item ID="IDI-1-18">
<CheckSum>1092308776</CheckSum>
<IsValid>1</IsValid>
<UserCode>B400L</UserCode>
<LinkCode></LinkCode>
<ManufCode>B400</ManufCode>
<Description>
<![CDATA[Base cabinet single door ]]></Description>
<Class>111</Class>
<Instance>Standard</Instance>
<ItemTypeName>Cabinets</ItemTypeName>
<ItemType>0</ItemType>
<SubTypeName>Base</SubTypeName>
<SubType>0</SubType>
<ChargeType>0</ChargeType>
<EOType>-1</EOType>
<IsCorner>0</IsCorner>
<IsPlaced>1</IsPlaced>
<IsCustom>0</IsCustom>
<IsWallMounted>0</IsWallMounted>
<IsFloorstanding>1</IsFloorstanding>
<Finish>Both</Finish>
<Hinge>Left</Hinge>
<Quantity>1</Quantity>
<IsForeign>0</IsForeign>
<HasForeign>0</HasForeign>
<Price Type="Cost" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="List" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Cost" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="List" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Cost" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="List" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Cost" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="List" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<PriceQuantity Type="Cost" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="List" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Retail" Mode="Fixed" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Cost" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="List" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Retail" Mode="Fixed" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Cost" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="List" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Retail" Mode="Fixed" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Cost" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="List" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<PriceQuantity Type="Retail" Mode="Fixed" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</PriceQuantity>
<ZoneID>-1</ZoneID>
<AbsolutePos x="164800" y="234800" z="12000"></AbsolutePos>
<Direction>0</Direction>
<NormDir>1</NormDir>
<Var ID="0" Type="Dim">
<Value>400</Value>
<Name>Width</Name>
</Var>
<Var ID="1" Type="Dim">
<Value>720</Value>
<Name>Height</Name>
</Var>
<Var ID="2" Type="Dim">
<Value>562</Value>
<Name>Depth</Name>
</Var>
<FeatureSetRef>IDFS-1</FeatureSetRef>
<LineItemNumber>1</LineItemNumber>
</Item>
<Pricing>
<GroupTotal Group="Catalog">
<Reference>
<![CDATA[KITMANIA]]></Reference>
<Name/>
<Total>
<BeforeTax>1</BeforeTax>
<Net>0</Net>
<Description>
<![CDATA[KITMANIA total]]></Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
<Total>
<BeforeTax>1</BeforeTax>
<Net>1</Net>
<Description>
<![CDATA[KITMANIA net total]]></Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
</GroupTotal>
<GroupTotal Group="ItemType">
<Reference>Cabinets</Reference>
<GroupReference>0</GroupReference>
<Total>
<BeforeTax>1</BeforeTax>
<Net>0</Net>
<Description>Cabinets total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
<Total>
<BeforeTax>1</BeforeTax>
<Net>1</Net>
<Description>Cabinets net total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
</GroupTotal>
<GroupTotal Group="ChargeType">
<Reference>Premium</Reference>
<GroupReference>1</GroupReference>
<Total>
<BeforeTax>1</BeforeTax>
<Description>Premium total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
</GroupTotal>
<GroupTotal Group="ItemSubType">
<Reference>Charges</Reference>
<GroupReference>4</GroupReference>
<Total>
<BeforeTax>1</BeforeTax>
<Description>Charges total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
</GroupTotal>
</Pricing>
<TotalWeight>0</TotalWeight>
<TotalVolume>0</TotalVolume>
</Catalog>
<Pricing>
<CurrencyCode></CurrencyCode>
<GroupTotal Group="Design">
<Total>
<BeforeTax>1</BeforeTax>
<Net>0</Net>
<Description>Design total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
<Total>
<BeforeTax>0</BeforeTax>
<Net>1</Net>
<Description>Design net total</Description>
<Price Type="Retail" Mode="Sum" Level="1">
<Value>129</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="2">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="3">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
<Price Type="Retail" Mode="Sum" Level="4">
<Value>0</Value>
<FormattedValue></FormattedValue>
</Price>
</Total>
</GroupTotal>
</Pricing>
</Design>
</Project>
I have just edited my question, sorry very new to this. However area of interest is (Var content):
<Var ID="0" Type="Dim">
<Value>400</Value>
<Name>Width</Name>
</Var>
<Var ID="1" Type="Dim">
<Value>720</Value>
<Name>Height</Name>
</Var>
<Var ID="2" Type="Dim">
<Value>562</Value>
<Name>Depth</Name>
</Var>
Here is where i want a XSL (as can specify in application) to convert this into the CSV format:
Width Height Depth
400 720 562
I can use multiple XSL's to get into alternate XML format prior to CSV format - as i've been looking at other Q&A on stackoverflow; and suspect this may be required beforehand.
In the past have used XmlToCsvConverter where the format of the CSV requires some attention.
Would appreciate any help, as i'm only just finiding my way - and suspect for the expert programmer this would be a walk in the park. Kind regards
Upvotes: 1
Views: 1229
Answers (1)
mellamokb
Reputation: 56769
While it's a little unclear what the exact requirements are or if this is the best approach, here is for example a piece of XSLT that would convert the XML you show above into a single row of CSV like your output example. Without a more complete sample, say of multiple rows, it's hard to generalize the solution (also your sample exported XML data is malformed because it's missing a root element):
<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text"/>
<xsl:template match="/">
<xsl:for-each select="//Var">
<xsl:value-of select="Name" /><xsl:text> </xsl:text>
</xsl:for-each><xsl:text>
</xsl:text>
<xsl:for-each select="//Var">
<xsl:value-of select="Value" /><xsl:text> </xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
Here's the tool I used to test: http://xslttest.appspot.com/, using the following slightly modified XML data (added a root element):
<Vars>
<Var ID="0" Type="Dim">
<Value>400</Value>
<Name>Width</Name>
</Var>
<Var ID="1" Type="Dim">
<Value>720</Value>
<Name>Height</Name>
</Var>
<Var ID="2" Type="Dim">
<Value>562</Value>
<Name>Depth</Name>
</Var>
</Vars>
Upvotes: 3