Oracle中xmltype类型toObject函数用法总结。
用法总结
【实例一】
xmltype是oracle中的type object类型。在实际使用中，可以当做xml对象来使用：
set serveroutput on
drop type person_typex;
create type person_typex is object (
name varchar2(32),
age number
);
/
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<root><NAME>John Doe</NAME><AGE>30</AGE></root>');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
执行结果
John Doe
30
PL/SQL procedure successfully completed.
【实例二】嵌套类型
注意嵌套类型对应到xml上，需要以嵌套类型的名字为根节点：参考下面例子的person字段。
set serveroutput on
drop type person_typexxx;
drop type person_typex;
create type person_typex is object (
name varchar2(32),
year number
);
/
create type person_typexxx is object (
namex varchar2(32),
person person_typex
);
/
set serveroutput on
declare
xmltype_var xmltype;
person_obj person_typexxx;
begin
xmltype_var := xmltype('<a123><NAMEX>John Doe</NAMEX><PERSON><NAME>John Doe</NAME><YEAR>30</YEAR></PERSON></a123>');
xmltype_var.toObject(person_obj);
end;
/
toObject函数实际将xmltype类型变量中的值，输出到自定义类型中。
那么这里最关键的是如何将xml的字段 mapping 到自定义类型中。
ORACLE文档中描述的是：
那么如果不提供schema的话，cannonical映射规则到底如何？文档中没有进一步描述。
下面做一些验证，这里总结下：
- 首先，必须是合法的XML类型。
- xml中的每一列都必须匹配到自定义类型中。不允许出现多余的列、重复的列。
- 顺序不能乱，指的是xml中可以缺少一些字段，但字段的出现顺序必须和自定义类型一致。
- 例如：
<a123><NAME>John Doe</NAME><YEAR>30</YEAR></a123> - 自定类型：
name varchar2(32), age number, year number是可以匹配的。 - 但如果：
<a123><YEAR>30</YEAR><NAME>John Doe</NAME></a123>是不能匹配的。
- 例如：
验证过程
位置不匹配？不支持；
多一列？不支持；
位置反了？不支持；
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<r1><NAME>John Doe</NAME><AA>a</AA><AGE>30</AGE></r1>');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
ORA-19031: XML element or attribute AA does not match any in type SYS.PERSON_TYPEX
缺后一列？支持；
缺前一列？支持；
set serveroutput on
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<r1><NAME>John Doe</NAME></r1>');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
数据类型不对？不支持
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<r1><NAME>John Doe</NAME><AGE>aaa</AGE></r1>');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
ORA-01722: invalid number
xml两层？不支持
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<outter><root><NAME>John Doe</NAME><AGE>30</AGE></root></outter');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
ORA-31011: XML parsing failedORA-19202: Error occurred in XML processing
xml根节点是任意值？支持
declare
xmltype_var xmltype;
person_obj person_typex;
begin
xmltype_var := xmltype('<a123><NAME>John Doe</NAME><AGE>30</AGE></a123>');
xmltype_var.toObject(person_obj);
dbms_output.put_line(person_obj.name);
dbms_output.put_line(person_obj.age);
end;
/
简单类型Postgresql替代方法
简化一下，输入为：
<a123><NAME>John Doe</NAME><YEAR>30</YEAR></a123>
输出元组
{NAME = 'John Doe', YEAR = 30}
在PostgreSQL中，使用xmltable函数可以实现类似效果：
select * from
xmltable(
'/a123'
passing '<a123><name>John Doe</name><YEAR>30</YEAR></a123>'
columns
name text,
"YEAR" int
);
复合类型Postgresql替代方法
测试1：无法展开
drop type ctype01;
create type ctype01 as (name text, year int);
select * from
xmltable(
'/a123'
passing '<a123><person><name>John Doe</name><year>30</year></person></a123>'
columns
person ctype01
);
结果
ERROR: malformed record literal: "John Doe30"
DETAIL: Missing left parenthesis.
测试2：没有展开
展开明显是没实现的。
record只能从xml读取括号的行格式。
drop type ctype01;
create type ctype01 as (a int, b int);
select * from
xmltable(
'/a123'
passing '<a123><person>(100, 200)</person></a123>'
columns
person ctype01
);
结果
person
-----------
(100,200)
测试3
select * from
xmltable(
'/a123'
passing '<a123><name>John Doe</name><year>30</year></a123>'
columns
person ctype01,
year int,
name text
);
结果文章来源：https://www.toymoban.com/news/detail-490617.html
person | year | name
--------+------+----------
| 30 | John Doe
测试4：table_to_xml导出表（有复合类型）结构
drop type i_type;
create type i_type as (i1 int, i2 int);
drop type o_type;
create type o_type as (o1 int, o2 i_type);
create table tbl_x01 (a int, b o_type);
insert into tbl_x01 values (1, (1, (100,200)::i_type)::o_type);
insert into tbl_x01 values (2, row(2, (200,300)::i_type));
insert into tbl_x01 values (3, (3, (300,400)));
postgres=# select * from tbl_x01;
a | b
---+-----------------
1 | (1,"(100,200)")
2 | (2,"(200,300)")
3 | (3,"(300,400)")
使用table_to_xml导出，会展开内层结构吗：不会。文章来源地址https://www.toymoban.com/news/detail-490617.html
postgres=# select table_to_xml('tbl_x01', false, false, '');
table_to_xml
-----------------------------------------------------------------------
<tbl_x005F_x01 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">+
+
<row> +
<a>1</a> +
<b>(1,"(100,200)")</b> +
</row> +
+
<row> +
<a>2</a> +
<b>(2,"(200,300)")</b> +
</row> +
+
<row> +
<a>3</a> +
<b>(3,"(300,400)")</b> +
</row> +
+
</tbl_x005F_x01> +
(1 row)
结论：不支持复合类型使用xmltable展开。
到了这里，关于Oracle中xmltype类型toObject函数用法实例的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
