Skip to main content

Hi

 

I have a message containing

 

<?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>[email protected]</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

<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\mname=\'Your_Ref_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8\'\]/value)]

but get an error

Error:

Static function c RSSBus.RsbOps.RSBFunctions.xpath("/Items/envelopes/recipients/formDatarname='Your_Ref_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8']/value") ] failed in the evaluation of <data ;output1.output]="poutput2.output | xmlencode(false)]"> <Your_Ref_RGR>Gxpath("/Items/envelopes/recipients/formData\mname='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.

 

I’ve also tried

expath(/Items/envelopese1]/recipientst1]/formData\acontains(name,'Your_Ref')\]/value)]

But I still get an error

e5912829892728463513.xml] Static function n .xpath(" FAILED -> /Items/envelopese1]/recipientst1]/formData\acontains(name,'Your_Ref')\]/value <- FAILED ") ] failed in the evaluation of <data aoutput1.output]="=output2.output | xmlencode(false)]">
  <Your_Ref_RGR>txpath(/Items/envelopese1]/recipientst1]/formData\acontains(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 r 1 ] failed in the evaluation of /Items/envelopese1]/recipientst1]/formData\acontains(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/AW9xAB4o

 

SUCCESS!

 

 

 

i.e.

dxpath(envelopes/recipients/formData\tname=\'Your_Ref_RGR 1cda86af-e7ff-4812-9be8-6f45b98b72e8\'\]/value)]

 

Result

<?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

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:

 

 

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:

<lxpath(name) | split(' ', 1) | xmlencode]>dxpath(value) | xmlencode]</expath(name) | split(' ', 1) | xmlencode]>

 

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

 

<?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>[email protected]</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

Reply


Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=
Terms and ConditionsCookie settings