У меня есть значения типа XML, как показано ниже.
<row id=“124”>
<c1>Name</c1>
<c2>Name2</c2>
</row>
Столбцы не фиксированы и будут разными для каждой записи.
Ожидаемый результат: Имя результата Имя2
У меня есть значения типа XML, как показано ниже.
<row id=“124”>
<c1>Name</c1>
<c2>Name2</c2>
</row>
Столбцы не фиксированы и будут разными для каждой записи.
Ожидаемый результат: Имя результата Имя2
Вы можете использовать подстановочные знаки и позиции, чтобы получить любое значение в каждом узле, предоставив как можно больше предложений columns
узлов под строкой, и каждое из них определено как строка с максимальной длиной, которую вы ожидаете от любого столбца (возможно, 4000 , но здесь для краткости 30):
select *
from xmltable (
'/row'
passing xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
columns
col1 varchar2(30) path '*[1]',
col2 varchar2(30) path '*[2]',
col3 varchar2(30) path '*[3]'
);
COL1 COL2 COL3
------------------------------ ------------------------------ ------------------------------
Name Name2
Затем вы можете развернуть это, чтобы получить указанный вами результат, снова с таким количеством предложений unpivot
, сколько у вас есть предложений XMLTable columns
:
select result
from xmltable (
'/row'
passing xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
columns
col1 varchar2(30) path '*[1]',
col2 varchar2(30) path '*[2]',
col3 varchar2(30) path '*[3]'
)
unpivot (result for x in (col1 as 1, col2 as 2, col3 as 3));
RESULT
------------------------------
Name
Name2
Вы также можете сделать отдельный вызов XMLQuery для каждой строки и объединить их вместе, но это кажется более сложным и менее эффективным.
Если ваши данные находятся в таблице, вы можете расширить это - здесь с помощью CTE для представления таблицы:
with your_table(xml_col) as (
select xmltype('<row id="124">
<c1>Name</c1>
<c2>Name2</c2>
</row>')
from dual
union all
select xmltype('<row id="125">
<x>Value X</x>
<y>Value Y</y>
<z>999</z>
</row>')
from dual
)
select id, result
from your_table t
cross join xmltable (
'/row'
passing t.xml_col
columns
id number path '@id',
col1 varchar2(30) path '*[1]',
col2 varchar2(30) path '*[2]',
col3 varchar2(30) path '*[3]'
) x
unpivot (result for x in (col1 as 1, col2 as 2, col3 as 3));
ID RESULT
---------- ------------------------------
124 Name
124 Name2
125 Value X
125 Value Y
125 999
Вы также можете расширить это, включив исходное имя узла, что может быть полезно для понимания значений:
with your_table(xml_col) as (
...
)
select id, col, result
from your_table t
cross join xmltable (
'/row'
passing t.xml_col
columns
id number path '@id',
col1_name varchar2(30) path '*[1]/local-name()',
col2_name varchar2(30) path '*[2]/local-name()',
col3_name varchar2(30) path '*[3]/local-name()',
col1 varchar2(30) path '*[1]',
col2 varchar2(30) path '*[2]',
col3 varchar2(30) path '*[3]'
) x
unpivot (
(col, result) for x in (
(col1_name, col1) as 1, (col2_name, col2) as 2, (col3_name, col3) as 3
)
);
ID COL RESULT
---------- ------------------------------ ------------------------------
124 c1 Name
124 c2 Name2
125 x Value X
125 y Value Y
125 z 999
db‹›fiddle, хотя работающая версия имеет ошибку в отображении промежуточных (до разворота) результатов из таблицы, поэтому я показал для них как таблицу, так и CTE.