How to get an XMLElement in resultset

[varsha rao]

Hi ,

I have the following query

SELECT z.itr_sys_id,
(SELECT XMLElement("AMOUNT",XMLAgg(XMLElement("wmm_match_am", wmm_match_am)))
FROM wire_mti_match x
WHERE x.itr_sys_id = z.itr_sys_id
) AS Amount
FROM icms_transaction z


The result returned is as follows
--------------------------------------------------------------
156565 <AMOUNT><wmm_match_am>500</wmm_match_am><wmm_match_am>500</wmm_match_am><wmm_match_am>0</wmm_match_am></AMOUNT>

But when i do resultset.getString("Amount") , i get null
I am not able to read the result into an appropriate java object.

Could anyone help me with this please?

Thanks

[Michael Angstadt]

Try creating an alias for that XML column:

SELECT z.itr_sys_id, 
(SELECT XMLElement(...)) AS xml_column
FROM ...

Then calling:

resultset.getString("xml_column");

[varsha rao]

Hey,

I have precisely done the same thing which you said. I also did some more research on this.

http://www.dbvis.com/forum/message.jspa?messageID=7512

You need to convert it to XMLType.

Unforunately this didnt work on Oracle10g .


Has someone tried the same on 10g?

Thanks