XML to CSV with data header
Question asked by Tony Hargreaves - January 3 at 1:46 PM
Unanswered
I have an XML file that describes a header and a list. I need to generate a CSV file where the first line of the file is constructed from the header information and the subsequent lines of the file constructed from the list. The header has a different number of columns to the list items.

This is easy to do with xsl. How can I do it with the data mapper using the .NET mapping engine?
 
A simple example to explain. XSD of input xml
    <xs:element name="Root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="A" />
                <xs:element name="B" />
                <xs:element name="Line" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="C" />
                            <xs:element name="D" />
                            <xs:element name="E" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>

An Example Input XML
<Root>
    <A>Header Value</A>
    <B>112233</B>
    <Line>
        <C>22</C>
        <D>Fred</D>
        <E>1</E>
    </Line>
    <Line>
        <C>34</C>
        <D>Jim</D>
        <E>2</E>
    </Line>
    <Line>
        <C>42</C>
        <D>Amanda</D>
        <E>1</E>
    </Line>
    <Line>
        <C>1267</C>
        <D>Vickie</D>
        <E>2</E>
    </Line>
</Root>

Required Text Output:
Header Value|112233
22|Fred|1
34|Jim|2
42|Amanda|1
1267|Vickie|2


Generating either
Header Value|112233
or
22|Fred|1
34|Jim|2
42|Amanda|1
1267|Vickie|2
as individual csv files is easy (as it should be) but how can I can't see how to produce the required file.

I have considered working around and generating a sequence of strings for each line. In this case I cannot see how to push the header onto the front of the sequence. If I use 'sequence', it flattens the list of lines (If this workaround was possible my objection would be that the semantics of the data are lost.)

I don't think this is a particularly obscure thing to want to do. At present, because of the ability to visually map data, we want to recommend Liquid to our customers as a solution to some of their EDI problems. Not having this facility blocks that process.
 

Reply to Thread