CODEWITHSUNDEEP
xmloracle-databaseplsql
elfoz
elfoz

Reputation: 115

PL/SQL parse clob data

I have a clob data like that:

I have two GAIN AND PAY (it can be more three, four..)

<G_1>
    <G><GAIN>14660</GAIN><PAY>0</PAY><Gc>1</Gc><S>1</S><D>0</D><K>0</K></G>
    <G><‌​GAIN>0</GAIN><PAY>0</PAY><T>0</T><S>0</S><M>0</M><W>1</W></G>
</G_1>

I want to reach 14660 and 0 (GAIN) and 0 s (pay) seperately. How can i do this ?

Upvotes: 0

Views: 221

Answers (1)

APC
APC

Reputation: 146209

Assuming your payload is actually XML, and the <AL>0</GRUP_AL> is just a typo, you could use an XPath query like this:

select
    xmlcast(
            xmlquery('/G_1/G/GAIN' passing XMLTYPE(col1) returning content)
            as NUMBER
           ) gain
    , xmlcast(
            xmlquery('/G_1/G/PAY' passing XMLTYPE(col1) returning content)
            as NUMBER
           ) pay
from t69
/

Check out the SQL Fiddle here.

So the new payload features multiple instances of G/GAIN and G/PAY. The previous solution produces the wrong answer because it concatenates the different instances into a single column.

For situations like this we need to use XMLTable(), like this:

select  xr.*
from t69
     ,  xmltable ('/G_1'
          passing xmltype (t69.col1) 
          columns g_set XMLTYPE PATH 'G') xt
     , xmltable( 'G'   
           passing xt.g_set
          columns gain number path 'GAIN'
                  , pay number path 'PAY'
           ) xr
/

Alternate SQL Fiddle here. Note that we get a NULL values for an instance of <G> without elements of <GAIN> or <PAY>.

Upvotes: 3

Related Questions