Extract value from XML

Question

I am using this query to extract the value from the XML

SELECT extractvalue(column_value, '/TransactionLimitDTO/idTxn') "TxnId"
FROM TABLE(XMLSequence(XMLType(mg.limits)
            .extract('/ModifyTransactionLimitRequestDTO/transactionLimit/TransactionLimitDTO'))) t,Mstgloballimitspackage mg

But, I'm getting an error:

ORA-00904: "MG"."LIMITS": invalid identifier

Could you please tell me how can I resolve this?

I tried this:

 SELECT extractvalue(column_value, '/TransactionLimitDTO/idTxn') "TxnId"
 FROM TABLE(XMLSequence(XMLType(select mg.limits from Mstgloballimitspackage mg)
                          .extract('/ModifyTransactionLimitRequestDTO/transactionLimit/TransactionLimitDTO'))) t

but this is also not working.

When I put a complete XML file in the place of mg.limits I get a too long string literal as a argument error.

It is working whenever I place a small piece of XML in the place of mg.limits.

score 1 · Accepted Answer · edited Dec 13 '12 at 09:02

1

This will work:

 SELECT extractvalue(t.column_value, '/TransactionLimitDTO/idTxn') "TxnId"
     FROM Mstgloballimitspackage mg,
          TABLE(XMLSequence(XMLType(mg.limits,'')
                            .extract('/ModifyTransactionLimitRequestDTO/transactionLimit/TransactionLimitDTO'))) t

edited Dec 13 '12 at 09:02

Ben

51,770
36
127
149

answered Dec 13 '12 at 07:19

venki

1,121
1
9
18

Your answer would be better if you could explain _why_ this works; it will help people who come to this question in the future. – Ben Dec 13 '12 at 09:03

Extract value from XML

1 Answers1