XML to CSV with data header using Liquid Data Mapper

Question

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 other than resorting back to xslt.

What have you tried so far? – Eric Hauenstein Jan 03 '19 at 14:08 — Eric Hauenstein, Jan 03 '19 at 14:08

score 0 · Answer 1 · answered Jan 03 '19 at 17:19

You can set up a transform that looks something like this if you want to be able to dynamically set the headers.

You double up the 'Row' inputs using the 'Duplicate' option

Before you do that you need to tweak your schema. The input values A-E need typing (note the type="xs:string").

<?xml version="1.0" encoding="utf-8" ?>
<!--Created with Liquid Studio 2019 (https://www.liquid-technologies.com)-->
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="A" type="xs:string" />
                <xs:element name="B" type="xs:string" />
                <xs:element name="Line" maxOccurs="unbounded">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="C" type="xs:string" />
                            <xs:element name="D" type="xs:string" />
                            <xs:element name="E" type="xs:string" />
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

This will give you output data that looks like this (note it always outputs 3 columns, so you get a trailing '|').

Header Value|112233|
22|Fred|1
34|Jim|2
42|Amanda|1
1267|Vickie|2

If you just want the data and no header then you can just connect it up like this

XML to CSV with data header using Liquid Data Mapper

1 Answers1