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;

輸出的結果執行個體:
<?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