Skip to main content
Solved

How to loop comma seperated values in XML node into multiple XML nodes with each value in?

  • August 23, 2024
  • 6 replies
  • 240 views
Origin-AnthonyR
Forum|alt.badge.img

Hi,

 

We have an XML file that contains a node called /Items/delivery_header/delivery_line_item/serial_numbers and in that node we have a list of serial numbers separated by commas if there is more than one (e.g. 1 serial would just be XYZABC but 2 or more would be XYZABC,XYZDEF).

 

Is there a way on the destination XML to create a loop that can iterate through the comma separated values and create XML nodes like the following:

 

for a single serial number

 

SerialNumber

      ManufacturerVersion = XYZABC

 

or for multiple serial number

 

SerialNumber

      ManufacturerVersion = XYZABC

SerialNumber

      ManufacturerVersion = XYZDEF

 

I have got as far as the below in the XML Map designer, I assume that I need to use a Loop with the nodes under it, but not sure what to use to split the values out, populate the ManufacturerVersion and then repeat with the next value:

 

 

Any help would be appreciated.

 

Thanks,

 

Anthony.

Best answer by Chaitanya Sandra

Hi Anthony, 

Thank you for the detailed explanation here. I'm from CData Arc Support following up on your request here.
 

What you're asking for here is a little tricky in XML Map, because the connector is designed to loop over anything that is addressed via XPath, but your comma-separated list of values is another array to loop over. The best way to handle this is to use ArcScript to load the list into a variable in memory using the map item:

https://cdn.cdata.com/help/AZK/mft/XML-Map-Advanced.html#map-item

And then use the arc:enum keyword to enumerate over that as a list - anything between the arc:enum tags is rendered once per entry in the list, with [_value] resolving to the entry in the list and [_index] returning a one-based index, if you want that. 

https://cdn.cdata.com/help/AZK/mft/op_arcEnum.html

Visually, this looks like this (the connector is attached):


 

Steps followed to achieve this:

 

1. Created a virtual loop on the SerialNumber as shown in the above and map with the SerialNumber.

2. Created a Variable []ManufacturerVersion with the ManufacturerVersion from the source.

3. Created a Code Script with the following Code using arc:enum to enumerate over the attributes within a variable called [_map.ManufacturerVersion ], a delimited list. Remember that we are not closing the arc:enum here.
 


4. Assign [_value] to the desired field: The value of the attribute being iterated over. For a multivalued attribute, the expand and separator parameter settings determine whether the _value is a reference to one attribute value or a reference to all attribute values.

5. At the end, you can close the enumeration.

 

Attached is the connector with an update for your reference, please let us know if this helps.

      This topic has been closed for replies.

      6 replies

      Chaitanya Sandra

      Hi Anthony, 

      Thank you for the detailed explanation here. I'm from CData Arc Support following up on your request here.
       

      What you're asking for here is a little tricky in XML Map, because the connector is designed to loop over anything that is addressed via XPath, but your comma-separated list of values is another array to loop over. The best way to handle this is to use ArcScript to load the list into a variable in memory using the map item:

      https://cdn.cdata.com/help/AZK/mft/XML-Map-Advanced.html#map-item

      And then use the arc:enum keyword to enumerate over that as a list - anything between the arc:enum tags is rendered once per entry in the list, with [_value] resolving to the entry in the list and [_index] returning a one-based index, if you want that. 

      https://cdn.cdata.com/help/AZK/mft/op_arcEnum.html

      Visually, this looks like this (the connector is attached):


       

      Steps followed to achieve this:

       

      1. Created a virtual loop on the SerialNumber as shown in the above and map with the SerialNumber.

      2. Created a Variable []ManufacturerVersion with the ManufacturerVersion from the source.

      3. Created a Code Script with the following Code using arc:enum to enumerate over the attributes within a variable called [_map.ManufacturerVersion ], a delimited list. Remember that we are not closing the arc:enum here.
       


      4. Assign [_value] to the desired field: The value of the attribute being iterated over. For a multivalued attribute, the expand and separator parameter settings determine whether the _value is a reference to one attribute value or a reference to all attribute values.

      5. At the end, you can close the enumeration.

       

      Attached is the connector with an update for your reference, please let us know if this helps.

        Chaitanya Sandra

        Hi Anthony,

        Here is the sample output response with the above ArcScript:
         

         

          Origin-AnthonyR
          Forum|alt.badge.img

          Hi Chaitanya,

          This was a great solution to the problem and helped me to understand the next steps.

          Unfortunately I was using an older version of Arc and so had to modify my steps a little bit.

          I found that the Loop was not needed as the source XML only contained a single node called “serial_numbers” which contained the comma separated list and that this only appeared once in each of the parent ForEach anyway.

          So I simply placed a Script, then the SerialNumber node, with ManufacturerVersion node as a child and then the final Scrip to stop the enum.

          I have tested this with a single, multiple and empty value “serial_numbers” node on the source and found that it correctly outputs either a single, multiple or none of the SerialNumber → ManufacturersVersion nested XML elements in destination.

          Your help was greatly appreciate, and probably one of the best answers I have had on a community forum.

          Thank you,

          Anthony.

            D
            Forum|alt.badge.img

            <?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">
              <data json:array="true">
                <id json:type="number">1723332</id>
                <idn>12</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1722421</id>
                <idn>11</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1718880</id>
                <idn>10</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1715006</id>
                <idn>9</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1714955</id>
                <idn>8</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1713986</id>
                <idn>7</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712786</id>
                <idn>6</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712739</id>
                <idn>5</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712729</id>
                <idn>4</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1709314</id>
                <idn>3</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1703633</id>
                <idn>2</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1701673</id>
                <idn>1</idn>
              </data>
            </Items>  how arc script mutiple idn get 

             

            D
            Forum|alt.badge.img

            <?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">
              <data json:array="true">
                <id json:type="number">1723332</id>
                <idn>12</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1722421</id>
                <idn>11</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1718880</id>
                <idn>10</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1715006</id>
                <idn>9</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1714955</id>
                <idn>8</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1713986</id>
                <idn>7</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712786</id>
                <idn>6</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712739</id>
                <idn>5</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1712729</id>
                <idn>4</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1709314</id>
                <idn>3</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1703633</id>
                <idn>2</idn>
              </data>
              <data json:array="true">
                <id json:type="number">1701673</id>
                <idn>1</idn>
              </data>
            </Items>   how to use all idn below arc 

             


            <!-- Read XML -->
            <arc:set attr="input.uri" value="[FilePath]"/>
            <arc:call op="xmlOpen" input="input">
              <arc:set attr="xml.handle" value="[handle]" />
            </arc:call>
            <arc:set attr="xml.map:data" value="/items/data"/>
            <arc:enum range="1..[output.header:idn]">
            <arc:try>
              <arc:call op="xmlDOMGet" in="xml" out="out1">
                <arc:set attr="data.idn"     value="[out1.idn]"/>
              </arc:call>
              
            <!-- Log Information -->
            <arc:set attr="_log.info">
            idn = [data.idn]
            </arc:set>


            <!-- fetch Shipment Details from DigiHaul -->
            <arc:set attr="http.URL" value="https://api.domain.com/sales/[data.idn]" />  
            <arc:set attr="http.header:name#" value="TOKEN" />
            <arc:set attr="http.header:value#" value="a6566566666666666fgdfgdfgdfgdfgdfg"/>


            <arc:set attr="http.verbosity" value="5"/> 
            <arc:set attr="http.logfile" value="D:\cData\logs\my.log"/> 


            <arc:set attr="_log.info">
            Auth = [http.header:*]
            URL = [http.URL]  
            </arc:set>  

            <arc:call op="httpGet" in="http">
              <arc:set attr="output.data" value="[http:content]" />
            </arc:call>

            <arc:set attr="_log.info">
            Response = [output.data]
            </arc:set>    

            <arc:set attr="output.filename" value="dh-fetch-order-[data.idn].json" />
            <arc:push item="output" />

              <arc:finally>
                <arc:call op="xmlClose" input="xml"/>
              </arc:finally>
            </arc:try>
              </arc:enum>
             

            D
            Forum|alt.badge.img

            example please

            State of AI Data Connectivity Report: 2026 Outlook
            Terms and ConditionsCookie settingsAccessibility statement