Generate XML with complex structures in Oracle

In Oracle, we can use the DBMS_XML package to convert the data found in SQL statements into XML files. However, sometimes we need a complex multi-layer structure of XML.

Compare the following two structures
A --- a1 ---
A --- a1 ---
A --- a1 ---
B --- b1 ---
B --- b1 ---
C --- c1 ---

A ---
A1 ---
A2 ---
A3 ---
B ---
B1 ---
B2 ---
C ---
C1 ---

For the xml of the first structure, we only need to associate A and a to query and generate XML.
Here I want to explain how to generate the XML of the Second Structure

Instance scenario:
Table XML_PRIMARY
Table XML_SECONDARY
The two tables are associated with foreign keys. Primary is the master table, and Secondary is the slave table of 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 );

Question: How to display the table data in an independent structure to generate XML
Solution: query the table data in the form of a data set.

1. Create an object type homogeneous to the slave table
Create or replace type Typ_o_Xml_Secondary AS OBJECT
(
S_Id NUMBER,
S_Name VARCHAR2 (10 ),
P_Id NUMBER
)
2. Create a table type for the slave table
Create or replace type TYP_XML_SECONDARY as table of TYP_O_XML_SECONDARY
3. Create a master table type with a set of table data
Create or replace type Typ_o_Xml_Primary AS OBJECT
(
P_Id NUMBER,
P_Name VARCHAR2 (20 ),
S_Data Typ_Xml_Secondary
)
4. Use Cast and Multiset to convert a subquery into a subresult set.
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)
Typ_Xml_Secondary) AS Xml_Primary
FROM Xml_Primary
5. Convert SQL into XML
Create or replace procedure Prc_Xml_Subxml
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)
Typ_Xml_Secondary) AS Xml_Primary
FROM Xml_Primary
');
RESULT: = Dbms_Xmlgen.Getxml (Qryctx );
Dbms_Output.Put_Line (RESULT );
END Prc_Xml_Subxml;

Example of the output result:
<? 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>

Follow-up Study:
1. In fact, if you only want data, there is no need to format it again with the Typ_o_Xml_Primary type. The following statement can achieve the goal:
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
However, the result changes the <S_DATA> label in XML to <CAST (MULTISET (SEL ......>, so here I use a structure to generate the tag name.
Or
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
You can also remove the tag name similar to <CAST (MULTISET (SEL...>, but you cannot name the outermost tag of XML.
In short, the benevolent sees benevolence, the wise sees wisdom

This article is from the "data ocean" blog, please be sure to keep this source http://alvin.blog.51cto.com/210283/91259