Skip to main content

I have an XML file generated by a specific SW, and there is an issue with one element, which content I cannot influence as I would like to. So I decided to try to update it with XMLUpdater, but I am just walking in the circles.

The (very simplified) file looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<gdb:Utilities xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:atr="atr"... etc.>
  <gdb:Data>
      <gdb:BiogasStations>
        <bgs:Records>
          <bgs:Record>
            ...
                <atr:IDZone>58KVK-96</atr:IDZone>
                <atr:IDSpecific>0</atr:IDSPecific>
            ...
          </bgs:Record>
          <bgs:Record>
            ...
                <atr:IDZone>58KVK-07</atr:IDZone>
                <atr:IDSpecific>0</atr:IDSPecific>
            ...
          </bgs:Record>
          <bgs:Record>
            ...
                <atr:IDZone>18LIK-15</atr:IDZone>
                <atr:IDSpecific>0</atr:IDSPecific>
            ...
          </bgs:Record>
          ...
        </bgs:Records>
      </gdb:BiogasStations>
  </gdb:Data>
</gdb:Utilities>

I would like to replace the content of the IDSpecific element with newSpecificID values from an XLS spreadsheet, which is very simple:

rowID newSpecificID
0 11890
1 11891
2 11892

The desired result would look like the following code. The order of newSpecificIDs replaced does not matter, it would be first-come, first-served. 

<?xml version="1.0" encoding="UTF-8"?>
<gdb:Utilities xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:atr="atr"... etc.>
  <gdb:Data>
      <gdb:BiogasStations>
        <bgs:Records>
          <bgs:Record>
            ...
                <atr:IDZone>58KVK-96</atr:IDZone>
                <atr:IDSpecific>11890</atr:IDSPecific>
            ...
          </bgs:Record>
          <bgs:Record>
            ...
                <atr:IDZone>58KVK-07</atr:IDZone>
                <atr:IDSpecific>11891</atr:IDSPecific>
            ...
          </bgs:Record>
          <bgs:Record>
            ...
                <atr:IDZone>18LIK-15</atr:IDZone>
                <atr:IDSpecific>11892</atr:IDSPecific>
            ...
          </bgs:Record>
          ...
        </bgs:Records>
      </gdb:BiogasStations>
  </gdb:Data>
</gdb:Utilities>

But I was not able to achieve this. When I used the whole file as an XML input, every record was updated with the last value of 11892. When I used Records as XML input, I got everything three times, the value being again 11892. I tried to look around and thought that building a list from newSpecificIDs and using a loop would help, but I have not been able to implement them, my XQuery knowledge is superficial. This XMLUpdater setup did not work, my newbie loop merely put all IDS inside the element together. I will be thankful for any help.

Update Type XML Path Value Type Value
Replace contents //atr:IDSpecific XML/XQuery 
for $record in fme:get-list-attribute("_list{}.newSpecificID")
return $record

 

 

Updating can be very tricky, and XML just makes the task worse.

Instead you might want to consider using XMLFragmenter/XMLFlattener to extract the relevant data from the old XML, then update normally in FME merging the new data from the spreadsheet, and lastly use XMLTemplater to rebuild the XML, using Sub Template’s to output the updated bgs:Record’s.

Hope this helps.

Hello @horlich 

The XML Path is incorrect for the type of update you want to perform. Your XPath selects any atr:IDSpecific node to update, so it will update all atr:IDSpecific elements sequentially. atr:IDSpecific is over-written by 11890, then 11891, and finally by 11892.

To get the result you want, add the bgs:Record node and its position to the XPath. You can use the attribute value of rowID and offset by one as XPath index starts at 1, and not 0.

Update Type XML Path Value Type Value
Replace contents //bgs:Record[@Value(rowID)+1]/atr:IDSpecific Plain Text

@Value(newSpecificID)

Hi @debbiatsafe, thank you very much! I had no idea that I could “mix” the ID into the XPath like this. And I see that Value and fme-get-attribute are in fact quite different things. Very useful. Now it seems so simple, and it works perfectly.

Hey @lifalin2016, thanks for the suggestion. I actually tried to avoid this, considering it to be a huge pain, as there are lots of XSD files involved. Nevertheless, when I read your answer, I decided to give a try to XML parsing with Python and ElementTree, which worked, so now I have two solutions for one problem 🍻.

Thanks a lot!

Community Stats

32,041
Posts
121,677
Replies
39,662
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.