oracle中multiset,Oracle中生成复杂结构的XML

在Oracle中,我们可以通过DBMS_XML包来将SQL语句查出的数据转成XML文件.但有时候我们需要的是复杂的多层结构的XML.

比较以下两个结构

A --- a1 ---

A --- a1 ---

A --- a1 ---

B --- b1 ---

B --- b1 ---

C --- c1 ---

A ---

a1 ---

a2 ---

a3 ---

B ---

b1 ---

b2 ---

C ---

c1 ---

对于第一种结构的XML,我们只需要简单的将A和a关联查询并生成XML即可

这里我要说明的是如何生成第二种结构的XML

实例场景:

表XML_PRIMARY

表XML_SECONDARY

两个表有外键的关联,Primary是主表,Secondary是Primary的从表

create table XML_PRIMARY

(

P_ID   NUMBER not null,

P_NAME VARCHAR2(20)

);

alter table XML_PRIMARY

add constraint PK_XML_PRIMARY primary key (P_ID)

using index;

create table XML_SECONDARY

(

S_ID   NUMBER not null,

S_NAME VARCHAR2(10),

P_ID   NUMBER

);

alter table XML_SECONDARY

add constraint PK_XML_SECONDARY primary key (S_ID)

using index;

alter table XML_SECONDARY

add constraint FK_XML_PRIM_SECO foreign key (P_ID)

references XML_PRIMARY (P_ID);

问题: 如何将从表的数据以独立的结构生成XML显示出来

解决办法: 将从表的数据以数据集合的形式查询出来

1. 建立与从表同构的对象类型

CREATE OR REPLACE TYPE Typ_o_Xml_Secondary AS OBJECT

(

s_Id   NUMBER,

s_Name VARCHAR2(10),

p_Id   NUMBER

)

2. 建立从表的表类型

CREATE OR REPLACE TYPE TYP_XML_SECONDARY AS TABLE OF TYP_O_XML_SECONDARY

3. 建立带从表数据集合的主表类型

CREATE OR REPLACE TYPE Typ_o_Xml_Primary AS OBJECT

(

p_Id   NUMBER,

p_Name VARCHAR2(20),

s_Data Typ_Xml_Secondary

)

4. 利用Cast和Multiset将子查询转化为子结果集

SELECT Typ_o_Xml_Primary(p_Id,

p_Name,

CAST(MULTISET (SELECT s_Id, s_Name, p_Id

FROM Xml_Secondary

WHERE p_Id = Xml_Primary.p_Id) AS

Typ_Xml_Secondary)) AS Xml_Primary

FROM Xml_Primary

5. 将SQL转化为XML

CREATE OR REPLACE PROCEDURE Prc_Xml_Subxml AS

Qryctx Dbms_Xmlgen.Ctxhandle;

RESULT CLOB;

BEGIN

Qryctx := Dbms_Xmlgen.Newcontext('

SELECT Typ_o_Xml_Primary(p_Id,

p_Name,

CAST(MULTISET (SELECT s_Id, s_Name, p_Id

FROM Xml_Secondary

WHERE p_Id = Xml_Primary.p_Id) AS

Typ_Xml_Secondary)) AS Xml_Primary

FROM Xml_Primary

');

RESULT := Dbms_Xmlgen.Getxml(Qryctx);

Dbms_Output.Put_Line(RESULT);

END Prc_Xml_Subxml;

输出的结果实例:

1

Primary data 1

11

Sec 11

1

12

Sec 12

1

13

Sec 13

1

2

Primary data 2

21

Sec 21

2

22

Sec 22

2

3

Primary data 3

31

Sec 31

3

后续研究:

1. 其实,如果只是要数据的话,没有必要用Typ_o_Xml_Primary类型再做一次格式化,如下语句即可达到目的

SELECT p_Id,

p_Name,

CAST(MULTISET (SELECT s_Id, s_Name, p_Id

FROM Xml_Secondary

WHERE p_Id = Xml_Primary.p_Id) AS Typ_Xml_Secondary)

FROM Xml_Primary

但,结果会让XML中的标签变成,所以这里我用了一个结构来生成标签的名字.

或者

SELECT p_Id,

p_Name,

CAST(MULTISET (SELECT s_Id, s_Name, p_Id

FROM Xml_Secondary

WHERE p_Id = Xml_Primary.p_Id) AS Typ_Xml_Secondary) as XXX

FROM Xml_Primary

也可以去掉类似于的标签名,但就无法命名XML最外层的标签.

总之,仁者见仁,智者见智吧 :)