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最外层的标签.