• Home
  • Forums
  • FME Form
  • Transformers
  • How do I extract XML that is stored in the _response_body attribute as separate records and use them to make a single table where particular XML tags represent the table fields? I've tried many transformers/approaches with no success...
Solved

How do I extract XML that is stored in the _response_body attribute as separate records and use them to make a single table where particular XML tags represent the table fields? I've tried many transformers/approaches with no success...

  • 8 October 2021
  • 6 replies
  • 16 views
G

Unfortunately, the source data can't be obtained as one package. Instead it can only be retrieved through individual SOAP calls. As such, the workflow obtains a dynamically generated list of relevant ID numbers retrieved through an initial GET request and then uses those ID numbers to construct the respective URLs for the individual GET requests, which obtain the data that I need (but it stores the XML retrieved through each GET request as a separate record/attribute). The workflow is for a regular batch update process using the externally sourced data to update my data.

icon

Best answer by nielsgerrits 11 October 2021, 13:11

View original
1 Attachment

6 replies

redgeographics
Rank
Userlevel 5
Badge +25

The XMLFragmenter and/or XMLFlattener should be able to do this. Can you post a sample of the XML?

G

The XMLFragmenter and/or XMLFlattener should be able to do this. Can you post a sample of the XML?

Hmmmm, yeah - I thought so too. However, I couldn't seem to get either the XMLFragmenter or XMLFlattener to pull out the XML from the _response_body attribute. I'm thinking that I probably haven't referenced the tags correctly to pull out the XML. I haven't used these particular tools before, but my different referencing attempts either didn't run or just created an additional field containing either the same _response_body attribute or empty values.

 

This is an example of the XML stored against each record. I've had to redact some of the result information due to privacy requirements that exist in relation to the XML content.

Redacted XML Example 

 

caracadrian
Rank
Badge +20

You can also write the HTTP Response Body as a file and read it via FeatureReader. It's more disk intensive but easier to process than XML Fragmenter/Flattener

nielsgerrits
Rank
Userlevel 6
Badge +33

Hmmmm, yeah - I thought so too. However, I couldn't seem to get either the XMLFragmenter or XMLFlattener to pull out the XML from the _response_body attribute. I'm thinking that I probably haven't referenced the tags correctly to pull out the XML. I haven't used these particular tools before, but my different referencing attempts either didn't run or just created an additional field containing either the same _response_body attribute or empty values.

 

This is an example of the XML stored against each record. I've had to redact some of the result information due to privacy requirements that exist in relation to the XML content.

Redacted XML Example 

 

The trick with the XMLFragmenter (and XMLFlattener) is to save the response to a sample file, then configure the transformer using the sample file as source, and when configured switch the source to attribute. When you use the file as source, you can select what you want from a tree structure.

XmlFragmenterParametersElementsToMatchSee attached workspace template.

Like @caracadrian​ suggests, saving the response as a text (FeatureWriter, Text File) and reading the written file as XML (FeatureReader) is also an option. But it will be slower due to the extra I/O. If you don't like to clean up the written files you can also use a TempPathnameCreator to write the file to the temp files.

G

Hmmmm, yeah - I thought so too. However, I couldn't seem to get either the XMLFragmenter or XMLFlattener to pull out the XML from the _response_body attribute. I'm thinking that I probably haven't referenced the tags correctly to pull out the XML. I haven't used these particular tools before, but my different referencing attempts either didn't run or just created an additional field containing either the same _response_body attribute or empty values.

 

This is an example of the XML stored against each record. I've had to redact some of the result information due to privacy requirements that exist in relation to the XML content.

Redacted XML Example 

 

Thanks so much nielsgerrits! Your explanation and screen prints were exactly what I needed. I'd seen references in another post to saving the response as text, but didn't understand that it was a temporary hack to expose the element tree.

G

Thanks everyone for your help! Really appreciate it!! :-)

Reply


Community Stats

28,724
Posts
109,516
Replies
36,561
Members
Terms & ConditionsCookie settings
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.