Database Reference
In-Depth Information
The point to make about Figure 17.12 is that all duplication cannot be
removed; thus the duplicated artist tags cannot be removed. The reason
why is as follows: Even if an XMLAGG function could contain another
embedded XMLAGG function, the GROUP BY clause cannot have more
than a single layer. There are alternative methods of solving this multilay-
ered duplication issue. Obviously, other XML generation methods can be
used. Additionally, a CAST(MULTISET(… into a nested table for each
subset may help. Other obvious answers are a FROM clause inline view
embedded subquery and using PL/SQL, which may be the best option.
Another point to make is that if programming languages have to be resorted
to at the second layer of a hierarchy, then something like PL/SQL may be
the better option than SQL/XML. In PL/SQL or another programming
language, the complex query we have been using would be a simple multi-
layered nested cursor procedure, dumping values using the
DBMS_OUTPUT procedure. Therefore, I will not pursue this topic any
further using SQL/XML. See Chapter 24 for details on PL/SQL.
The SYS_XMLGEN function in the next section shows multilayered
capabilities using CAST(MULTISET(… functionality and user-defined
types. I still think PL/SQL might be easier to code.
17.2.1.2.2
The SYS_XMLGEN Function
The
SYS_XMLGEN
function creates an XML document for each row
read. Unfortunately, this function does not appear to work properly in my
current release of Oracle Database 10
g
, but this is more or less how it is sup-
posed to work. In general, it passes subset row arrays into subset type arrays
(nested tables).
CREATE OR REPLACE TYPE tSONG AS OBJECT(
TITLE VARCHAR2(64), RECORDING_DATE DATE
, PLAYING_TIME CHAR(10));
/
CREATE OR REPLACE TYPE tSONG_LIST AS TABLE OF tSONG;
/
CREATE OR REPLACE TYPE tARTIST AS OBJECT(
NAME VARCHAR2(32), CITY VARCHAR2(32)
, COUNTRY VARCHAR2(32), SONG_LIST tSONG_LIST);
/
SELECT SYS_XMLGEN(tARTIST(A.NAME, A.CITY, A.COUNTRY,
CAST(MULTISET(SELECT tSONG(S.TITLE
, S.RECORDING_DATE, S.PLAYING_TIME)
