Блог

У меня есть значения типа 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 для них.