XML to CSV component

The XML to CSV component provides a direct conversion from the XML format to the CSV format.

Configuration

The XML to CSV component has the following configuration options:

• Include header
• Include index column
• Index column name
• Delimiter
• Line separator
• Sort columns
• Quote fields
• XPath expression

Include header

Options

• yes (default)
• no

Description

This option allows you to determine whether to include a header in the CSV output.

Example

Notice the difference in the first line in the CSV. When yes is selected, a header line is added. When no is selected, no header line is added.

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

CSV - 'no' is selected

"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

CSV - 'yes' is selected

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Include index column

Options

• yes
• no (default)

Description

This option allows you to determine whether to include an index column in the CSV output.

Remark

When this option is set to yes, the index column will always be the first column, and the line numbers will always be without quotes.

Example

Notice the difference in the first column in the CSV. When no is selected, no index column is added. When yes is selected, an index column is added.

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

CSV - 'no' is selected

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

CSV - 'yes' is selected

"line","name","price","description"
1,"food1","1","desc1"
2,"food2","2","desc2"
3,"food3","3","desc3"

Index column name

Prerequesites

To enable this option, the Include index column must be set to yes.

Description

This option allows you to specify the name of the index column that is to be added in the CSV output. The default value for this option is line.

Delimiter

Description

This option allows you to specify the character that separates the column values in the CSV output. The default value for this option is ,.

Line separator

Options

• line feed (\n) (default)
• carriage return (\r)
• end of line (\r\n)

Description

This option allows you to determine the character that separates the lines in the CSV output.

Sort column names

Options

• original (default)
• ascending
• descending

Description

This option allows you to determine how the columns must be sorted (by their names) in the CSV output.

Example

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

CSV - 'original' is selected

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

CSV - 'ascending' is selected

"description","name","price"
"desc1","food1","1"
"desc2","food2","2"
"desc3","food3","3"

CSV - 'descending' is selected

"price","name","description"
"1","food1","desc1"
"2","food2","desc2"
"3","food3","desc3"

Quote fields

Options

• all fields (default)
• non integer fields
• no fields

Description

This option allows you to determine which fields must be quoted in the CSV output.

Example

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
</breakfast_menu>

CSV - 'all fields' is selected

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

CSV - 'non integer fields' is selected

"name","price","description"
"food1",1,"desc1"
"food2",2,"desc2"
"food3",3,"desc3"

CSV - 'no fields' is selected

name,price,description
food1,1,desc1
food2,2,desc2
food3,3,desc3

XPath expression

Description

This options allows you to specify an XPath expression that determines the content of the XML that is to be processed.

Example

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<breakfast_menu>
    <food>
        <name>food1</name>
        <price>1</price>
        <description>desc1</description>
    </food>
    <food>
        <name>food2</name>
        <price>2</price>
        <description>desc2</description>
    </food>
    <food>
        <name>food3</name>
        <price>3</price>
        <description>desc3</description>
    </food>
    <drink>
        <name>drink1</name>
        <price>1</price>
    </drink>
    <drink>
        <name>drink2</name>
        <price>2</price>
    </drink>
</breakfast_menu>

CSV - XPath expression is set to '//drink'

"name","price"
"drink1","1"
"drink2","2"

CSV - XPath expression is set to '//food'

"name","price","description"
"food1","1","desc1"
"food2","2","desc2"
"food3","3","desc3"

Remarks

The XML should conform to the following XML structure: (note that the processing is supported up to the subSubElement level)

<root>
  <element>
    <subElement>
      <subSubElement></subSubElement>
    </subElement>
  </element>
</root>

Example

<?xml version="1.0" encoding="UTF-8"?>    <!-- structure tags: -->
<items>                                   <!-- root -->
  <item>                                  <!-- element -->
    <firstName>Joe</firstName>            <!-- subElement -->
    <lastName>Foo</lastName>              <!-- subElement -->
    <age>21</age>                         <!-- subElement -->
    <description>                         <!-- subElement -->
      <title>Title</title>                <!-- subSubElement -->
      <body>Body</body>                   <!-- subSubElement -->
    </description>                        <!-- subElement -->
  </item>                                 <!-- element -->
  <item>                                  <!-- element -->
    <firstName>John</firstName>           <!-- subElement -->
    <lastName>Doe</lastName>              <!-- subElement -->
    <age>30</age>                         <!-- subElement -->
    <description>                         <!-- subElement -->
      <title>Title</title>                <!-- subSubElement -->
      <body>Body</body>                   <!-- subSubElement -->
    </description>                        <!-- subElement -->
  </item>                                 <!-- element -->
</items>                                  <!-- root -->

Furthermore, it is important to be aware of the following remarks:

• Only one type of element will be processed (the first one that is encountered).
• Header names are chosen based on the unique subElements and subSubElements. The name of the subSubElements will contain the subElement name at the beginning like so: subElement_subSubElement (separated by an underscore).
• Multiple subElements or subSubElements with the same name inside the same element are ignored outside the first one. (Thus, the only exception is when the subElements and subSubElements with the same name exist on different levels.)
• In the following cases the XML should be manually adjusted or the XSLT component should be used to make the XML usable by the XML to CSV component:
  • When attributes need to be processed.
  • When multiple types of elements need to be processed.
  • When non-unique subElements or subSubElements inside the same element need to be processed.