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. | 370 Using XML in Oracle Figure Duplicating Parent Tags. Artist duplicated Artist Name Sheryl Crow City Corvalis City Country US A Country CD TiUe Soak Up the Sun Genre American Soft Rock Released 28- FEB-01 Released Price Price Song Titie Soak Up The Sun Album Version Track 1 Recorded 11-NOV-00 Recorded Length 11 20 Length Song CD Artist Artist Name Sheryl Crow City Corvalis City Country USA Country CD duplicated CD Titte Soak Up the Sun Genre American Soft Rock Released 28-FEB-01 Released Price Price Song Title Soak Up The Sun Sunsweep Radio Mix Track 2 Recorded 15-DEC-00 Recorded Length 3 2 0 Length Song __ I CD Artist later in this chapter. For now all I have done is copy the first two rows in Figure and pasted and annotated them into Figure . 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 clearly shows that duplication 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 is much better than that of Figure but it is still not correct as can be seen by appropriate annotations in Figure because artists remain duplicated. SELECT XMLELEMENT Artist XMLATTRIBUTES Name Please purchase PDF Split-Merge on to remove this watermark. .
