Question

Extract key value pairs from XML

  • 29 November 2022
  • 5 replies
  • 48 views
nedwaterman
Rank
Badge +3

Morning,

 

I'm trying to extract logger data from an api returning XML. I can use the XMLFlattener to match my message element and return the pt element as an xml fragment. However I need to then expose all the key value pairs in the <pt></pt> element into attributes and if I try to use the AttributeExposer there's a huge list, which will change when i run the api with different data ranges (ie more key value pairs to manually expose).

Is there any way I can automatically expose all of the elements within the <pt></pt> xml element one column each of time, value, ch, chtype etc to insert into a db table?

 

Cheers!

 

 

1 Attachment

5 replies

nielsgerrits
Rank
Userlevel 6
Badge +32

You can do this using an XMLFragmenter. Elements to match:

messages/message/message/pt

 Check the enable flattening box and expose the flattened attributes.

nedwaterman
Rank
Badge +3

Thank you! I had not put in the forward slashes, so the xml elements weren't being fragmented. Cheers for you help!

nedwaterman
Rank
Badge +3

Hi Niels

This is working but the original values (such as the logger id) are missing. Any idea how I can carry these through?

kailinatsafe
Rank
Userlevel 3
Badge +13

Hello @nedwaterman​, take a look at the attached workspace and let me know if this workspace accomplishes the goal. It seems like only data messages will carry a <pt> element. I recommend using an AttributeRemover or AttributeKeeper to tidy the attributes afterwards! This article is also a great resource for extracting key/value pairs that come out of XML, KML, etc (see Method 2). Happy to help, Kailin.

nedwaterman
Rank
Badge +3

Hello @nedwaterman​, take a look at the attached workspace and let me know if this workspace accomplishes the goal. It seems like only data messages will carry a <pt> element. I recommend using an AttributeRemover or AttributeKeeper to tidy the attributes afterwards! This article is also a great resource for extracting key/value pairs that come out of XML, KML, etc (see Method 2). Happy to help, Kailin.

Thanks Kallin - I'll check it out!

Reply


Community Stats

28,661
Posts
109,324
Replies
36,528
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.