xml - 如何在Oracle中使用PL/SQL解析XML属性而不是XML元素？

请参阅下面的pl/sql代码片段：

create table t23 (
    field01 number,
    field02 number,
    field03 char(1)
);

示例代码片段1

declare x varchar2(2000) := '
<ArrayOfRecords>
<Record>
<Field01>130</Field01>
<Field02>1700</Field02>
<Field03>C</Field03>
</Record>
<Record>
<Field01>131</Field01>
<Field02>1701</Field02>
<Field03>B</Field03>
</Record>
</ArrayOfRecords>';

begin
    insert
    into    T23
    SELECT  *
    FROM    XMLTABLE('/ArrayOfRecords/Record'
                PASSING xmltype(x)
                    COLUMNS
                    Field01 number PATH 'Field01',
                    Field02 number PATH 'Field02',
                    Field03 char(1) PATH 'Field03'
            );
end;

-- The records will be here - great!
select  * from T23;

示例代码片段2

declare y varchar2(2000) := '
<ArrayOfRecords>
<Record Field01="130" Field02="1700" Field03="C" />
<Record Field01="131" Field02="1701" Field03="B" />
</ArrayOfRecords>';

begin
    insert
    into    T23
    SELECT  *
    FROM    XMLTABLE('/ArrayOfRecords/Record'
                PASSING xmltype(y)
                    COLUMNS
                    Field01 number PATH 'Field01',
                    Field02 number PATH 'Field02',
                    Field03 char(1) PATH 'Field03'
            );
end;

-- null values are inserted into the table - not great
select  * from T23;

如何从示例片段2中的xml属性中获取值？有没有办法告诉xmltype从属性和元素中检索数据？

最佳答案

对属性使用@语法

select *
  from xmltable('/ArrayOfRecords/Record'
         passing xmltype(y)
         columns
         field01 number path '@Field01',
         field02 number path '@Field02',
         field03 char(1) path '@Field03'
       );