Skip to main content
Solved

Which XML transformer and query is most appropriate to extract this XML?

  • March 16, 2023
  • 6 replies
  • 59 views

daniel.m.getz
Contributor
Forum|alt.badge.img+4

I have this block of XML.

<?xml version="1.0" encoding="UTF-16"?><place place_id="11703">

  <gas_price type="regular">22.19</gas_price>

  <gas_price type="premium">24.19</gas_price>

 </place>

 

I used an XMLfragmenter to break up the big block of XML from the data source into the above snippets for different stations. But I can't figure out how to extract these using the XML transformers.

 

I am trying to get a result like this:

 

ID | Price Premium | Price Regular

11703 | 24.19 | 22.19

 

I know I'll probably have to transpose the data, but even before then I can't get the XML extractor or flattener to do anything to this little JSON snippet. Is there an obvious query to extract this using one of the transformers? Thanks

Best answer by ebygomm

This returned a result, but only if I selected Single value as the return value, and said yes to the XML Header.

 

In the end, the result only ended up being the XML version information.

<?xml version="1.0" encoding="UTF-8" ?>

 

Any idea why I might be getting the result this way?

Did you expose the attributes? This is my test with the xml you provided

image

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

6 replies

jkr_wrk
Influencer
Forum|alt.badge.img+35
  • 424 replies
  • March 16, 2023

First of all, the xml utf-16 encoding seems to give errors in my workbench (2021.2) so I had to strip that away with for example a stringreplacer.

 

You can use a XMLFlattener to create unexposed attributes containing the values you are looking for:

Elements to match: place

Attributes to Expose: place.place_id, gas_price{},gas_price{}.type

 

Then you can use a attributeCreator with 2 Conditional Values.

new attribute: Price Premium

Value: 3 Possible Values

 

Test Condition:

If @Value(gas_price{0}.type = premium Then value is gas_price{0}

If @Value(gas_price{1}.type = premium Then value is gas_price{1}

Else <No Action>

 

And repeat this for the gas price regular.

 

If you have locations with lots and lots more prices (diesel, lpg, electric, mega-diesel, liquid-nitrogen, green electric etc. etc.) a listsearcher could save a lot of test cases.

 

 

 


ebygomm
Influencer
Forum|alt.badge.img+44
  • Influencer
  • 3422 replies
  • March 16, 2023

You could use an XMLXQueryExtractor, if your fragment is in an attribute called xml

let $p :=fme:get-xml-attribute("xml")/place
for $g in $p/gas_price
return (
fme:set-attribute("place", $p/@place_id/string()),
fme:set-attribute($g/@type/string(), $g/text())
)

XML Input = None

You would then just need to expose attributes place, premium and regular


daniel.m.getz
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • 9 replies
  • March 16, 2023

You could use an XMLXQueryExtractor, if your fragment is in an attribute called xml

let $p :=fme:get-xml-attribute("xml")/place
for $g in $p/gas_price
return (
fme:set-attribute("place", $p/@place_id/string()),
fme:set-attribute($g/@type/string(), $g/text())
)

XML Input = None

You would then just need to expose attributes place, premium and regular

This returned a result, but only if I selected Single value as the return value, and said yes to the XML Header. 

 

In the end, the result only ended up being the XML version information.

<?xml version="1.0" encoding="UTF-8" ?>

 

Any idea why I might be getting the result this way?


daniel.m.getz
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • 9 replies
  • March 16, 2023

First of all, the xml utf-16 encoding seems to give errors in my workbench (2021.2) so I had to strip that away with for example a stringreplacer.

 

You can use a XMLFlattener to create unexposed attributes containing the values you are looking for:

Elements to match: place

Attributes to Expose: place.place_id, gas_price{},gas_price{}.type

 

Then you can use a attributeCreator with 2 Conditional Values.

new attribute: Price Premium

Value: 3 Possible Values

 

Test Condition:

If @Value(gas_price{0}.type = premium Then value is gas_price{0}

If @Value(gas_price{1}.type = premium Then value is gas_price{1}

Else <No Action>

 

And repeat this for the gas price regular.

 

If you have locations with lots and lots more prices (diesel, lpg, electric, mega-diesel, liquid-nitrogen, green electric etc. etc.) a listsearcher could save a lot of test cases.

 

 

 

So this worked, but only partially. See the results here. ID 11699 returns the price and the type of fuel correctly. But look at row 11703. It has prices, but the flattener doesn't seem to find the price. just the ID.

 

It seems like 11699 only has a single fuel type per XML fragment. Any idea how I might query these '2 prices in one result' records?Image showing some correct types, some missing typesRow 11703 ResultsWorking one with single XML element


ebygomm
Influencer
Forum|alt.badge.img+44
  • Influencer
  • 3422 replies
  • Best Answer
  • March 16, 2023

This returned a result, but only if I selected Single value as the return value, and said yes to the XML Header.

 

In the end, the result only ended up being the XML version information.

<?xml version="1.0" encoding="UTF-8" ?>

 

Any idea why I might be getting the result this way?

Did you expose the attributes? This is my test with the xml you provided

image


daniel.m.getz
Contributor
Forum|alt.badge.img+4
  • Author
  • Contributor
  • 9 replies
  • March 16, 2023

This returned a result, but only if I selected Single value as the return value, and said yes to the XML Header.

 

In the end, the result only ended up being the XML version information.

<?xml version="1.0" encoding="UTF-8" ?>

 

Any idea why I might be getting the result this way?

I forgot to expose the attributes. Rookie mistake! Thank you for the help.