How to Extract the value of resultSet returned fro

Question

I have JDBC where I'm calling the stored Procedure, It is returning the response as below, But I'm pretty not sure how to extract the value of result set

Please find the response from DB

{updateCount1=4,resultSet1=[{XML_F5RYI-11YTR=<Customers><Customer1>John<Customer1><Customer2>Ganesh<Customer2><Customers>}],resultSet2[{SequenceNumber=94}],updateCount2=1, updateCount3=4}

I have used the this expression #[message.payload.get(0)], It has return the ResultSet as below, But not exactly value required. I need to take the xml value of XML_F5RYI-11YTR.

{XML_F5RYI-11YTR=<Customers><Customer1>John<Customer1><Customer2>Ganesh<Customer2><Customers>}

Also tried like below #[message.payload.get(0).XML_F5RYI-11YTR] but getting error , not able to extract the xml.

Could you please suggest how can I extract the xml from the ResultSet1

Answer 1

Thump rule - you database connector gives you list of map, not sure what format does it is carry, if you want XML_F5RYI.. value then do the below

[message.payload.get(0)] convert it to json or map from which #[message.payload.get("XML_F5RYI-11YTR")]

Answer 2

The database connector gives you a list of maps. The map keys will be the name of the columns. Therefore if you want to get updateCount1, you can use something like this:

#[payload.get('updateCount1')]"

Answer 3

In most cases, the way you did it should work. I think what is happening here is that the hyphen in the column name is interpreted by the MEL parser as a subtraction. So you could change yours to this syntax, and it should work:

#[message.payload.get(0)['XML_F5RYI-11YTR']]

Also you can omit "message", as payload is resolvable directly:

#[payload.get(0)['XML_F5RYI-11YTR']]

You could use array bracket syntax to access the first row in the result set, instead of the get method:

#[payload[0]['XML_F5RYI-11YTR']]

Finally, you might want to do something for each row returned from the database. If you use a collection-splitter or a for-each, your payload will be the map that represents the row, instead of a list of maps representing the whole result set:

<collection-splitter />
<logger message="#[payload['XML_F5RYI-11YTR']]" />

EDIT

To access the result set in the payload shown in the question, you would need to access it like so:

#[payload.resultSet1[0]['XML_F5RYI-11YTR']]