Solved

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

daniel.m.getz
Rank
Badge +1

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

icon

Best answer by ebygomm 16 March 2023, 18:17

View original

6 replies

jkr_wrk
Rank
Userlevel 3
Badge +17

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.

 

 

 

Sample: Every Nth Feature (N=1)
ebygomm
Rank
Userlevel 1
Badge +21

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
Rank
Badge +1

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
Rank
Badge +1

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
Rank
Userlevel 1
Badge +21

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
Rank
Badge +1

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.

Reply


Community Stats

28,696
Posts
109,442
Replies
36,546
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.