Skip to main content
Solved

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

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

daniel.m.getz wrote:

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

View original
Did this help you find an answer to your question?

6 replies

jkr_wrk
Influencer
Forum|alt.badge.img+29
  • jkr_wrkInfluencer
  • 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.

 

 

 

💡 Did you know... The FeatureWriter is more intuitive. 😏
ebygomm
Influencer
Forum|alt.badge.img+38
  • ebygommInfluencer
  • Influencer
  • 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
  • daniel.m.getzContributor
  • Author
  • Contributor
  • March 16, 2023
ebygomm wrote:

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
  • daniel.m.getzContributor
  • Author
  • Contributor
  • March 16, 2023
jkr_da wrote:

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+38
  • ebygommInfluencer
  • Influencer
  • Best Answer
  • March 16, 2023
daniel.m.getz wrote:

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
  • daniel.m.getzContributor
  • Author
  • Contributor
  • March 16, 2023
daniel.m.getz wrote:

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


Helpful Members This Week

Community Stats

31,341
Posts
118,858
Replies
38,930
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.