# Flatten XML before converting to CSV

**URL:** <https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217>\
**Category:** CData Arc\
**Tags:** question\
**Created:** [June 20, 2023, 8:00am UTC](https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217 "2023-06-20T08:00:03Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [June 20, 2023, 8:00am UTC](https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217/1 "2023-06-20T08:00:03Z")

</div>

Hi

I have a message containing

```xml
<?xml version="1.0" encoding="utf-8"?><Items xmlns:json="http://arc.cdata.com/ns/jsonconnector" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">	<envelopes json:array="true"> <envelopeId>a9b6d6d0-393a-4542-9441-ae622f84f8dd</envelopeId> <recipients json:array="true"> <recipientId>0da3068e-da57-42c5-a99a-432f0822aa59</recipientId> <email>russell@feeed.com</email> <name>Russell Hutson</name> <formData json:array="true"> <name>Your_Ref_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8</name> <value>65546546546</value> </formData> <formData json:array="true"> <name>Co_Address_Line1_RGR f53b822c-d976-43ce-b786-dbfa7c46be12</name> <value>Test 1</value> </formData> <formData json:array="true"> <name>Co_Add_Line2_RGR bc4630a4-3e29-49df-b22a-1f911439666e</name> <value>test 2</value> </formData> <formData json:array="true"> <name>Goods_Desc_RGR 5ef6d1e9-4b62-47bd-b6fa-7eff52f5900e</name> <value>Trousers</value> </formData> <formData json:array="true"> <name>Number_Pkgs_RGR 4418142a-f17d-4b52-b6cf-7e811366554e</name> <value>1.00</value> </formData> <formData json:array="true"> <name>Goods_Weight_RGR cafffa30-a2fb-4384-bb78-80e02e73c630</name> <value>1</value> </formData> <formData json:array="true"> <name>Goods_Value_RGR 4c1734e3-8d23-4f13-8e6e-01de00dccded</name> <value>£1.00 GBP</value> </formData> <formData json:array="true"> <name>Company</name> <value>Test</value> </formData> <formData json:array="true"> <name>Co_Post_Town_RGR 485f6550-3ce5-49fa-8d3a-d6ae7a7fd65e</name> <value>Test 3</value> </formData> <formData json:array="true"> <name>Co_Postcode_RGR 151348bf-6f56-4dda-8af0-1fb7dd6bdc46</name> <value>Test 4</value> </formData> <formData json:array="true"> <name>Goods_Comm_Code_RGR 2af9573e-1068-49dd-b38a-10ea82b273de</name> <value>123,456.00</value> </formData> <formData json:array="true"> <name>Goods_Reason_List_RGR aef8ffda-9cfd-4b04-badf-664a83197f34</name> <value>Faulty</value> </formData> <formData json:array="true"> <name>FullName</name> <value>Russell Hutson</value> </formData> <formData json:array="true"> <name>EORI_RGR 079837e9-d35e-4bd4-8eb0-712b1c4de552</name> <value>12345</value> </formData> <formData json:array="true"> <name>DateSigned</name> <value>6/19/2023</value> </formData> </recipients>	</envelopes></Items>

```

Which I need to flatten into a eventually to a CSV File.

How do I map it to become

```xml
<data> <Your_Ref_RGR>65546546546</Your_Ref_RGR> <Co_Address_Line1_RGR>Test 1</Co_Address_Line1_RGR>... <FullName>Russell Hutson</FullName> <EORI_RGR>12345</EORI_RGR></data>

```

I tried using a Lookahead in XML Map like this

[xpath(/Items/envelopes/recipients/formData\[name=\‘Your\_Ref\_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8\’\]/value)]

but get an error

**Error:**

Static function [RSSBus.RsbOps.RSBFunctions.xpath(“/Items/envelopes/recipients/formData[name=‘Your\_Ref\_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8’]/value”) ] failed in the evaluation of \<data [output1.output]=“[output2.output | xmlencode(false)]”\> \<Your\_Ref\_RGR\>[xpath(“/Items/envelopes/recipients/formData\[name=‘Your\_Ref\_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8’\]/value”) | def | xmlencode(false)]\</Your\_Ref\_RGR\> \<Co\_Address\_Line1\_RGR\>Test 1\</Co\_Address\_Line1\_RGR\> \<FullName\>Russell Hutson\</FullName\> \<EORI\_RGR\>12345\</EORI\_RGR\>\</data\>. The error was:Execute ‘RSSBus.RsbOps.RSBFunctions.xpath’ function failed: Exception has been thrown by the target of an invocation. Unable to find XML object, make sure the file has been loaded.

---

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [June 20, 2023, 11:12am UTC](https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217/2 "2023-06-20T11:12:53Z")

</div>

I’ve also tried

```auto
[xpath(/Items/envelopes[1]/recipients[1]/formData\[contains(name,'Your_Ref')\]/value)]

```

But I still get an error

```auto
[5912829892728463513.xml] Static function [.xpath(" FAILED -> /Items/envelopes[1]/recipients[1]/formData\[contains(name,'Your_Ref')\]/value <- FAILED ") ] failed in the evaluation of <data [output1.output]="[output2.output | xmlencode(false)]"> <Your_Ref_RGR>[xpath(/Items/envelopes[1]/recipients[1]/formData\[contains(name,'Your_Ref')\]/value)]</Your_Ref_RGR> <Co_Address_Line1_RGR>Test 1</Co_Address_Line1_RGR> <FullName>Russell Hutson</FullName> <EORI_RGR>12345</EORI_RGR></data>. The error was:Formatter [1] failed in the evaluation of /Items/envelopes[1]/recipients[1]/formData\[contains(name,'Your_Ref')\]/value. The error was:The value of the attribute could not be accessed: The attribute does not exist.

```

However, the same expression works [xpather.com](http://xpather.com)&nbsp;[http://xpather.com/AW9xAB4o](http://xpather.com/AW9xAB4o)

---

<div class="post-metadata">

**Author:** ![russell-jerseypost](https://avatars.discourse-cdn.com/v4/letter/r/439d5e/32.png) [@russell-jerseypost](https://community.cdata.com/u/russell-jerseypost)\
**Post date:** [June 20, 2023, 11:42am UTC](https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217/3 "2023-06-20T11:42:49Z")

</div>

SUCCESS!

 ![7d4065a0-a97f-4049-b169-3e7b0553619d.png](https://us1.discourse-cdn.com/cdata/original/1X/4d8e9bf6e134991edcb485e36e272cde9f5eba83.png)

i.e.

```auto
[xpath(envelopes/recipients/formData\[name=\'Your_Ref_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8\'\]/value)]

```

Result

```xml
<?xml version="1.0" encoding="UTF-8"?><data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <Your_Ref_RGR>65546546546</Your_Ref_RGR> <Co_Address_Line1_RGR>Test 1</Co_Address_Line1_RGR> <FullName>Russell Hutson</FullName> <EORI_RGR>12345</EORI_RGR></data>

```

So maybe cData Arc does not support the XPath `contains` predicate

---

<div class="post-metadata">

**Author:** ![James\_B](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/james_b/32/1688_2.png) [@James\_B](https://community.cdata.com/u/James_B)\
**Post date:** [June 20, 2023, 8:55pm UTC](https://community.cdata.com/t/flatten-xml-before-converting-to-csv/217/4 "2023-06-20T20:55:18Z")

</div>

It’s true that the XPath evaluation in Arc doesn’t support the contains predicate, but it looks to me like the best approach here would be to dynamically render output elements based on the contents of the formData array, which you can do with a New Code Script like so:

 ![6ced9b47-6011-4d0b-9664-1f6122c14e48.png](https://us1.discourse-cdn.com/cdata/original/2X/6/6ed7181ebe2dbcb121df4eb548299a7a6f2dde4a.png)

You can render literal XML output this way - you just need to xmlencode the values (this is also based on a possible need to split the element names).

The code that is in the script is this:

```xml
<[xpath(name) | split(' ', 1) | xmlencode]>[xpath(value) | xmlencode]</[xpath(name) | split(' ', 1) | xmlencode]>

```

And if I run your file through, I get this output:

```xml
<?xml version="1.0" encoding="UTF-8"?><data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <recipientID>0da3068e-da57-42c5-a99a-432f0822aa59</recipientID> <email>russell@feeed.com</email> <name>Russell Hutson</name> <Your_Ref_RGR>65546546546</Your_Ref_RGR> <Co_Address_Line1_RGR>Test 1</Co_Address_Line1_RGR> <Co_Add_Line2_RGR>test 2</Co_Add_Line2_RGR> <Goods_Desc_RGR>Trousers</Goods_Desc_RGR> <Number_Pkgs_RGR>1.00</Number_Pkgs_RGR> <Goods_Weight_RGR>1</Goods_Weight_RGR> <Goods_Value_RGR>£1.00 GBP</Goods_Value_RGR> <Company>Test</Company> <Co_Post_Town_RGR>Test 3</Co_Post_Town_RGR> <Co_Postcode_RGR>Test 4</Co_Postcode_RGR> <Goods_Comm_Code_RGR>123,456.00</Goods_Comm_Code_RGR> <Goods_Reason_List_RGR>Faulty</Goods_Reason_List_RGR> <FullName>Russell Hutson</FullName> <EORI_RGR>12345</EORI_RGR> <DateSigned>6/19/2023</DateSigned></data>

```

I’ve attached my connector (converted from .arcflow to .zip) here

[ConvertToCSV.zip](https://community.cdata.com/uploads/short-url/oqDd4bHtDLTZBMCErgZN8ER57JU.zip) (2.28 KB)