在 Oracle 中如何让 PL/SQL 解析 XML 属性而不是 XML 元素？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2222942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-18 19:59:00 来源:igfitidea点击:
How do you get PL/SQL to parse XML attributes instead of XML elements in Oracle?
提问by AlexIEEE
Please see PL/SQL snippets below:
请参阅下面的 PL/SQL 片段：
create table t23 (
field01 number,
field02 number,
field03 char(1)
);
Example Snippet #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;
Example Snippet #2
示例代码段 #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;
How do you get the values out of the XML attributes in example snippet #2? Is there a way to tell xmltype to retrieve data from attributes vs. elements?
如何从示例代码段 #2 中的 XML 属性中获取值？有没有办法告诉 xmltype 从属性和元素中检索数据？
回答by Doug Porter
Use the @syntax for attributes
使用@属性的语法
select *
from xmltable('/ArrayOfRecords/Record'
passing xmltype(y)
columns
field01 number path '@Field01',
field02 number path '@Field02',
field03 char(1) path '@Field03'
);
