Skip to main content
Solved

Removing similar records from XML

  • 5 July 2024
  • 2 replies
  • 42 views

Given a file like 

<?xml version="1.0" encoding="utf-8"?>
<statuses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <status>
    <StatusID>1056</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-02</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:37:09</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:37:09</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
  <status>
    <StatusID>1066</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-01</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:38:03</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:38:03</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
  <status>
    <StatusID>1066</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-01</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:38:03</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:38:03</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
  <status>
    <StatusID>1067</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-03</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:38:05</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:38:05</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
</statuses>

I want to reduce this down to just

 

<?xml version="1.0" encoding="utf-8"?>
<statuses xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <status>
    <StatusID>1056</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-01</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:37:09</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:37:09</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
  <status>
    <StatusID>1066</StatusID>
    <ReqDepot>183</ReqDepot>
    <ManifestDate>2024-07-05</ManifestDate>
    <DeliveryDate>2024-07-08</DeliveryDate>
    <ConNo>0000105</ConNo>
    <VigoID>0</VigoID>
    <TrackingCode>1831110000105</TrackingCode>
    <PalletID>1831110000105-01</PalletID>
    <StatusCode>DSO</StatusCode>
    <StatusTime>16:38:03</StatusTime>
    <StatusDate>2024-07-05</StatusDate>
    <Notes />
    <PODName />
    <PODTime>16:38:03</PODTime>
    <PODDate>2024-07-05</PODDate>
  </status>
</statuses>

The key to look out for is <ConNo> I need the last <status> node for each <ConNo> only.

 

Any thoughts on how to achieve this?

 

thanks in advance

2 replies

James B
Userlevel 6
Badge

Unfortunately, there really isn’t a simple way to loop over an XML record like this and filter out the duplicate records without involving some scripting of your own, because the XML Map connector does not include any elements like “Foreach Unique”. 

In a separate forum entry, I have written about script where you can group elements in a format like this together in groups based on a shared key column:
 

Grouping together records in a flat format (CSV) based on a shared key | Community (cdata.com)

And if you do this, you could later attempt to detect the last element in this set (although you provide ConNo as your key element in the example above, and all of the elements in the source share the same value here). 

 

 

There may be a better approach however - does this data start out as XML or is it in another format? Is the destination for this data a table (which you could potentially use to upsert records to the same column based on the key)? 

R
Userlevel 5
Badge +1

Thanks. I could potentially fire this off to a custom REST API and put the logic in the API endpoint.

I am beginning to use n8n.io as another useful tool in my EDI armoury. It’s not a replacement for cData Arc, but it can compliment it.

Reply


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