Oracle中xmltype类型toObject函数用法实例

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.

toObject函数实际将xmltype类型变量中的值，输出到自定义类型中。

那么这里最关键的是如何将xml的字段 mapping 到自定义类型中。

ORACLE文档中描述的是：

那么如果不提供schema的话，cannonical映射规则到底如何？文档中没有进一步描述。

下面做一些验证，这里总结下：

1. 首先，必须是合法的XML类型。
2. xml中的每一列都必须匹配到自定义类型中。不允许出现多余的列、重复的列。
3. 顺序不能乱，指的是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 failed
ORA-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;
/

PG联想

简化一下，输入为：

<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
);