CODEWITHSUNDEEP
xmloracle-databaseoracle11gnamespacesxmltable
maverick
maverick

Reputation: 35

How to use XMLTable in Oracle on the following XML

I have XML in a column in Oracle, stored as CLOB type. My intention is to parse this XML and retrieve data corresponding to columns of interest. This is the XML structure:

<ns:ABCDE xmlns:ns="http://www.something.com/something" xmlns:mno="http://www.somethingelse.com/05"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:type="ns:ABCDE"> 

<GGGGG> 
    <G1>G1_val</G1> 
    <G2>G2_val</G2> 
    <G3>G3_val</G3>
</GGGGG>
<PPPPP>
...
</PPPP>

</ns:ABCDE>

And this is the SQL code I have:

    select x.*,p.*
    from ppp p ,xmltable(

    xmlnamespaces(default   'ABCDE',
    'http://www.something.com/something' as "ns",
    'http://www.somethingelse.com/05' as "mno", 
'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
    'ns:ABCDE' as "type"
            ),    

    '/ns:ABCDE'
    PASSING xmltype(p.someCLOB_column)
    COLUMNS G1 varchar(32) PATH '/GGGGG'

    ) x

However, after running the SQL it does not show any error, but no records are fetched either. I'm expecting to get "G1_val" as the result of this query.

Where is the code going wrong? I'm not too familiar with xml/namespaces.

Thanks.

Upvotes: 1

Views: 337

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

I'm not sure why you aren't seeing anything; I see the wrong thing, but that may be down to Oracle version I suppose, or difference in your real XML and code. The default namespace is confusing the issue and shouldn't be there; and the path for GGGGG doesn't need the leading slash:

with ppp (someCLOB_column) as (
  select '<ns:ABCDE xmlns:ns="http://www.something.com/something" xmlns:mno="http://www.somethingelse.com/05"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="ns:ABCDE"> 
<GGGGG> 
    <G1>G1_val</G1> 
    <G2>G2_val</G2> 
    <G3>G3_val</G3>
</GGGGG>
<PPPPP>...</PPPPP>
</ns:ABCDE>' from dual
)
select x.g1
from ppp p
cross join xmltable(
  xmlnamespaces(--default 'ABCDE',
    'http://www.something.com/something' as "ns",
    'http://www.somethingelse.com/05' as "mno", 
    'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
    'ns:ABCDE' as "type"
  ),    
  '/ns:ABCDE'
  PASSING xmltype(p.someCLOB_column)
  COLUMNS G1 varchar(32) PATH 'GGGGG'
) x 
/

G1                             
--------------------------------
G1_valG2_valG3_val

You probably want the individual values though:

select x.g1, x.g2, x.g3, x.ppppp
from ppp p
cross join xmltable(
  xmlnamespaces(--default 'ABCDE',
    'http://www.something.com/something' as "ns",
    'http://www.somethingelse.com/05' as "mno", 
    'http://www.w3.org/2001/XMLSchema-instance' as "xsi",
    'ns:ABCDE' as "type"
  ),    
  '/ns:ABCDE'
  PASSING xmltype(p.someCLOB_column)
  COLUMNS G1 varchar(8) PATH 'GGGGG/G1',
    G2 varchar(8) PATH 'GGGGG/G2',
    G3 varchar(8) PATH 'GGGGG/G3',
    PPPPP varchar2(8) PATH 'PPPPP'
) x 
/

G1       G2       G3       PPPPP  
-------- -------- -------- --------
G1_val   G2_val   G3_val   ...

Upvotes: 1

Related Questions