Oracle SQL Jumpstart with Examples- P9

Oracle SQL Jumpstart with Examples- P9: Review"As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...
Nội dung trích xuất từ tài liệu:
Oracle SQL Jumpstart with Examples- P9 370 17.2 Using XML in Oracle Figure 17.11 Duplicating Parent Tags. later in this chapter. For now all I have done is copy the first two rows in Figure 17.10 and pasted and annotated them into Figure 17.11. Most relational database interpretation of XML is direct and dumps rows into two dimensions, as results would appear in row form, such as in this join. Note: Two-dimensional data is useful for platform-independent transfer between multiple databases. However, there are other, faster methods for achieving this task with Oracle Database. The beauty of XML is its potential object hierarchical nature, effectively allowing removal of duplicated data. Figure 17.11 clearly shows that dupli- cation is present in abundance. What can we do about this? We can use a function called XMLAGG to aggregate data. In its simplest form, XMLAGG is limited, because it appears to be capable of descending only into a single level of a hierarchy. XMLCONCAT does not help either in this respect because of conflict between the aggregation functions and the GROUP BY clause. The result of the following query as shown in Figure 17.12 is much better than that of Figure 17.11, but it is still not correct, as can be seen by appropriate annotations in Figure 17.12, because artists remain duplicated. SELECT XMLELEMENT(Artist, XMLATTRIBUTES(A.NAME Name)Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 17.2 Using XML in Oracle 371 , XMLFOREST(A.CITY City, A.COUNTRY Country) , XMLELEMENT(CD, XMLATTRIBUTES(CD.TITLE Title , G.GENRE Genre) , XMLFOREST(CD.PRESSED_DATE Released , CD.LIST_PRICE Price) , XMLAGG(XMLELEMENT(Song , XMLATTRIBUTES(S.TITLE Title , T.TRACK_SEQ_NO Track) , XMLFOREST(S.RECORDING_DATE Recorded , TRIM(S.PLAYING_TIME) Length)) ))).GETSTRINGVAL() 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) GROUP BY A.NAME, A.CITY, A.COUNTRY, CD.TITLE, G.GENRE , CD.PRESSED_DATE, CD.LIST_PRICE; Figure 17.12 XMLAGG Removes Lowest- Level Duplication Layer. Chapter 17Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 372 17.2 Using XML in Oracle 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 usin ...