XMLTable和GETXMLTYPE - 如何将XML转换为行？

I have the following XML:

我有以下XML:

<ROWSET>
 <ROW>
    <record_id>2</record_id>
    <catalog>Y</catalog>
  </ROW>
</ROWSET>

How do I get everything inside "ROW" as a row with name/value?

如何将“ROW”中的所有内容作为具有名称/值的行?

This is what I have so far:

这是我到目前为止:

WITH a AS
(
    SELECT DBMS_XMLGEN.getxmltype ('SELECT record_id, catalog FROM courses WHERE record_id = 1') AS xml_data
    FROM dual
)
SELECT w2.col_name, w2.col_value, a.xml_Data
FROM a, XMLTABLE('ROWSET/ROW/*' PASSING a.xml_data
                COLUMNS col_name PATH 'name',
                col_value PATH 'text()') w2

Basically I want:

基本上我想要:

col_name, col_value
record_id    2
catalog      Y

1 个解决方案

#1

Oracle XMLDB has powerfull support for XQuery and XQuery flower(FLWOR). Try this approach

Oracle XMLDB强大支持XQuery和XQuery花(FLWOR)。试试这种方法

with params as (
  select 
    xmltype('
      <ROWSET>
       <ROW>
          <record_id>2</record_id>
          <catalog>Y</catalog>
        </ROW>
      </ROWSET>
    ') p_xml
  from dual  
)    
select
  col_name, col_val
from
  XMLTable(
    '              
      for $i in $doc//descendant-or-self::*
        return <element>
                 <element_path> {$i/string-join(self::*/name(.), ''/'')} </element_path>
                 <element_content> {$i/text()}</element_content>
               </element> 
    '
    passing (select p_xml from params) as "doc"
    columns 
      col_name   varchar2(4000) path '//element_path',
      col_val   varchar2(4000) path '//element_content'
  )
where col_val is not null