Database Reference
In-Depth Information
Figure 17.9
A Complex Join of
Five Tables.
Now let's look at a much more complex application example. We start
out with a mutable join of five tables, as shown in the following query and
the result in Figure 17.9.
SELECT A.NAME "Artist", A.CITY "City", A.COUNTRY "Country"
, CD.TITLE "CD", G.GENRE "Genre"
, CD.PRESSED_DATE "Released", CD.LIST_PRICE "Price"
, S.TITLE "Song", T.TRACK_SEQ_NO "Track"
, S.RECORDING_DATE "Recorded", S.PLAYING_TIME "Length"
FROM ARTIST A
JOIN SONG S ON(S.ARTIST_ID = A.ARTIST_ID)
JOIN CDTRACK T ON(T.SONG_ID = S.SONG_ID)
JOIN MUSICCD CD ON(CD.MUSICCD_ID = T.MUSICCD_ID)
JOIN GENRE G ON(G.GENRE_ID = CD.GENRE_ID);
Now let's push that complex join query into an XML generation format,
as shown in the following script. The result is shown in Figure 17.10. Note
how the number of rows in Figure 17.10 are the same as in Figure 17.9.
SELECT XMLELEMENT("Artist", XMLATTRIBUTES(A.NAME "Name")
, XMLFOREST(A.CITY "City", A.COUNTRY "Country")
, XMLELEMENT("CD", XMLATTRIBUTES(CD.TITLE "Title"
