在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"?>        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最外层的标签. 总之,仁者见仁,智者见智吧 :)