Solved

XML Parsing

  • 8 October 2018
  • 6 replies
  • 30 views

Badge

Hi All, as part of a work flow i have to parse some XML that im reading via an API

I pass a job ID to the api and get this response back:

<Response api-method="Job/CustomField">
<Status>OK</Status>
<CustomFields>
<CustomField>
<ID>225109</ID>
<Name>Probability of win (%)</Name>
<Number>20</Number>
</CustomField>
<CustomField>
<ID>221125</ID>
<Name># Street Suburb City PO Box</Name>
<Text>71 Sunrise Ave Murrays Bay 900</Text>
</CustomField>
<CustomField>
<ID>219331</ID>
<Name>NZTM 2000 Easting</Name>
<Number>1755638</Number>
</CustomField>
<CustomField>
<ID>219352</ID>
<Name>NZTM 2000 Northing</Name>
<Number>5933369</Number>
</CustomField>
</CustomFields>
</Response>

I need to extract the "Number" value below the NZTM 2000 Easting and the "Number" Value below the NZTM 2000 Northin so i can create a vertex I have used the xml fragmenter and flattener to get close but not quite right... errors occur if the first "number" in the xml is empty (ie the probability of win %) and also the output is on 3 rows instead of one row with 3 columns :

i get this:

2017556385933369

i want this 

2017556385933369

Any help or suggestions much appreciated.. I have read the xml tutorials which were very helpful but im still running my "L" plates on xml parsing.....

Thanks Steve

icon

Best answer by nielsgerrits 8 October 2018, 08:23

View original

6 replies

Userlevel 6
Badge +31

What you want is to transpose the data (rows to columns). This can be done multiple ways but one way is:

  • Fragment (Save a sample of the XML, select file in the XMLFragmenter, open the elements to match and select what you need in the tree. In this case "CustomField".) Flatten and expose attributes in Fragmenter.
  • Explode the Attributes to Features. (Keep original attributes.)
  • Filter only the needed Features.
  • Create columns (Attribute Name from the value in the attribute "Name" and Attribute Value from the value "Number". You can now see them in the Inspector under Feature Information.)

  • Expose the created columns (As the previous part is dynamic, workbench can't expose these by itself.)
  • Merge the created columns (Aggregator, select Merge Incoming Attributes.)

parsexml2018.fmwt

Badge

What you want is to transpose the data (rows to columns). This can be done multiple ways but one way is:

  • Fragment (Save a sample of the XML, select file in the XMLFragmenter, open the elements to match and select what you need in the tree. In this case "CustomField".) Flatten and expose attributes in Fragmenter.
  • Explode the Attributes to Features. (Keep original attributes.)
  • Filter only the needed Features.
  • Create columns (Attribute Name from the value in the attribute "Name" and Attribute Value from the value "Number". You can now see them in the Inspector under Feature Information.)

  • Expose the created columns (As the previous part is dynamic, workbench can't expose these by itself.)
  • Merge the created columns (Aggregator, select Merge Incoming Attributes.)

parsexml2018.fmwt

Many Thanks Neils, Very helpful, I have understood what you have done and slightly modified the output to my requirements.(passed a unique ID all the way along and done a group by ID in the final aggregator to get an ID,Easting and Northing output for each record) i didnt need the Prob % win after all.

 

 

Thanks Again, Its always great to learn new ways to do things and i appreciate your input!

 

 

Steve

 

 

 

Badge +22

I would tackle this slightly differently, instead of fragmenting the response xml on the custom field, I would flatten it on either Response or CustomFields (depending on whether you want the api method and status as attributes) and then manipulate the resulting list.

 

 

Badge +22

Because I'm trying to teach myself XQuery,  it can be also be done in a single XMLXQueryExtractor.

XQuery expression:

for $node in //CustomField
let $key := string($node/Name)
let $val := $node/Number
return
if ($val)
then (fme:set-attribute($key,string($val)))
else (fme:set-attribute($key,string($node/Text)))

Userlevel 6
Badge +31

I would tackle this slightly differently, instead of fragmenting the response xml on the custom field, I would flatten it on either Response or CustomFields (depending on whether you want the api method and status as attributes) and then manipulate the resulting list.

 

 

ListKeyValuePairExtractor? Learned something new today, thanks @jdh!
Badge +22
ListKeyValuePairExtractor? Learned something new today, thanks @jdh!
There are a lot of useful list manipulation custom transformers on the hub.

 

 

Reply