PL SQL - выборка данных CLOB (XML) - все данные из тегов List и Struct of XML должны быть получены

Question

<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>

Я хочу получить данные как

Name   Value
ABC    abc
XYZ    xyz

Я сейчас пользуюсь

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

который выбирает только item[1] ценности. Но я хочу получить все значения в структуре элемента. Любая помощь будет оценена. Благодарю. PS: я даже пытался Extract вместо extractvalue, но он дает данные в формате тегов.

Answer 1

Вы можете использовать XQuery, в частности, здесь 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;

Вам необходимо правильно объявить свое пространство имен CSIM в своем XML-документе. Демо с добавлением 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