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

高铭杰

已于 2023-06-13 11:27:35 修改

阅读量2.4k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： oracle 文章标签： oracle 数据库 xmltype toobject

于 2023-06-12 20:40:42 首次发布

本文链接：https://blog.csdn.net/jackgo73/article/details/131176153

oracle 专栏收录该内容

13 篇文章

订阅专栏

文章详细介绍了Oracle中xmltype类型的toObject函数的使用，包括基本用法、嵌套类型的处理，以及字段映射的规则。同时，通过示例展示了当XML结构与自定义类型不匹配时可能出现的错误。此外，文章还探讨了PostgreSQL中如何使用xmltable函数来实现类似的功能，但指出PostgreSQL对复合类型的支持并不完善。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

简单类型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
);

结果

 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导出，会展开内层结构吗：不会。

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)