在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;
输出的结果实例:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<XML_PRIMARY>
<P_ID>1</P_ID>
<P_NAME>Primary data 1</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>11</S_ID>
<S_NAME>Sec 11</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>12</S_ID>
<S_NAME>Sec 12</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>13</S_ID>
<S_NAME>Sec 13</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
<ROW>
<XML_PRIMARY>
<P_ID>2</P_ID>
<P_NAME>Primary data 2</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>21</S_ID>
<S_NAME>Sec 21</S_NAME>
<P_ID>2</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>22</S_ID>
<S_NAME>Sec 22</S_NAME>
<P_ID>2</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
<ROW>
<XML_PRIMARY>
<P_ID>3</P_ID>
<P_NAME>Primary data 3</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>31</S_ID>
<S_NAME>Sec 31</S_NAME>
<P_ID>3</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
</ROWSET>
后续研究:
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中的<S_DATA>标签变成<CAST(MULTISET (SEL........>,所以这里我用了一个结构来生成标签的名字.
或者
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
也可以去掉类似于<CAST(MULTISET (SEL........>的标签名,但就无法命名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;
输出的结果实例:
<?xml version="1.0"?>
<ROWSET>
<ROW>
<XML_PRIMARY>
<P_ID>1</P_ID>
<P_NAME>Primary data 1</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>11</S_ID>
<S_NAME>Sec 11</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>12</S_ID>
<S_NAME>Sec 12</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>13</S_ID>
<S_NAME>Sec 13</S_NAME>
<P_ID>1</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
<ROW>
<XML_PRIMARY>
<P_ID>2</P_ID>
<P_NAME>Primary data 2</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>21</S_ID>
<S_NAME>Sec 21</S_NAME>
<P_ID>2</P_ID>
</TYP_O_XML_SECONDARY>
<TYP_O_XML_SECONDARY>
<S_ID>22</S_ID>
<S_NAME>Sec 22</S_NAME>
<P_ID>2</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
<ROW>
<XML_PRIMARY>
<P_ID>3</P_ID>
<P_NAME>Primary data 3</P_NAME>
<S_DATA>
<TYP_O_XML_SECONDARY>
<S_ID>31</S_ID>
<S_NAME>Sec 31</S_NAME>
<P_ID>3</P_ID>
</TYP_O_XML_SECONDARY>
</S_DATA>
</XML_PRIMARY>
</ROW>
</ROWSET>
后续研究:
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中的<S_DATA>标签变成<CAST(MULTISET (SEL........>,所以这里我用了一个结构来生成标签的名字.
或者
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
也可以去掉类似于<CAST(MULTISET (SEL........>的标签名,但就无法命名XML最外层的标签.
总之,仁者见仁,智者见智吧 :)
本文出自 “数据海洋” 博客，请务必保留此出处http://alvin.blog.51cto.com/210283/91259
