CODEWITHSUNDEEP
databaseoracle-databaseplsqlcloblob
krishna chaitanya
krishna chaitanya

Reputation: 93

PL SQL - CLOB (XML) data fetching - All the data from List and Struct of XML tags is to be fetched

<ENVVAR CSIM:dt="list">
<item CSIM:dt="struct">
<Category dt:dt="string">ENV</Category>

<Name dt:dt="string">ABC</Name>
<Value dt:dt="string">abc</Value>
</item>
<item CSIM:dt="struct">
<Category dt:dt="string">ENV</Category>
<Name dt:dt="string">XYZ</Name>
<Value dt:dt="string">xyz</Value>
</item>
</ENVVAR CSIM>

I want to fetch the data as 

Name   Value
ABC    abc
XYZ    xyz

I am currently using 

extractvalue (ABC.IOXML, '/ENVVAR/item[1]/Name') as ParaName
extractvalue (ABC.IOXML, '/ENVVAR/item[1]/Value') as ParaValue

which fetches only item[1] values. But I want to fetch all the values in the item structure. Any help will be appreciated. Thanks. PS: I even tried Extract instead of extractvalue, but it gives the data in the tags format.

Upvotes: 0

Views: 82

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

You can use an XQuery, specifically here XMLTable:

select x.*
from abc
cross join xmltable (
  '/ENVVAR/item'
  passing xmltype(abc.ioxml)
  columns name varchar2(20) path 'Name',
    value varchar2(20) path 'Value'
) x;

You need to declare your CSIM namespace properly in your XML document. Demo with added xmlns:

with abc(ioxml) as (
  select '<ENVVAR CSIM:dt="list" xmlns:CSIM="http://www.example.com" xmlns:dt="http://www.example.com">
<item CSIM:dt="struct">
<Category dt:dt="string">ENV</Category>
<Name dt:dt="string">ABC</Name>
<Value dt:dt="string">abc</Value>
</item>
<item CSIM:dt="struct">
<Category dt:dt="string">ENV</Category>
<Name dt:dt="string">XYZ</Name>
<Value dt:dt="string">xyz</Value>
</item>
</ENVVAR>' from dual
)
select x.*
from abc
cross join xmltable (
  '/ENVVAR/item'
  passing xmltype(abc.ioxml)
  columns name varchar2(20) path 'Name',
    value varchar2(20) path 'Value'
) x;

NAME                 VALUE              
-------------------- --------------------
ABC                  abc                 
XYZ                  xyz

Upvotes: 1

Related Questions