Database Reference
In-Depth Information
, T.TRACK_SEQ_NO "Track")
, XMLFOREST(S.RECORDING_DATE "Recorded"
, TRIM(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)
GROUP BY CD.MUSICCD_ID, A.NAME, A.CITY, A.COUNTRY, CD.TITLE
, G.GENRE, CD.PRESSED_DATE, CD.LIST_PRICE;
That was easy! Now let's find out how to retrieve XML data.
17.2.2.2
Retrieving from XML Documents
XMLType datatype column values can be retrieved using SQL SELECT
commands, XML extraction functions, and special Oracle text operators.
When extracting CLOB values, the SET LONG <lots> command is
required in SQL*Plus in order to show enough of the string value in the
CLOB object. SET LONG 80 is the default and restricts width to 80 char-
acters, which is not much when it comes to XML. Here are four simple
examples for showing entire XML value contents. The first two examples
will return the entire XML value in a single row on a single line. The third
and fourth examples will beautify the result, as shown in Figure 17.13. The
fourth example specifically must have SET LONG <lots> applied, other-
wise only one row will be returned.
SET LONG 2000;
SELECT X.XML.GETSTRINGVAL() AS Artist FROM XML X WHERE ID = 4;
SELECT X.XML.GETCLOBVAL() AS Artist FROM XML X WHERE ID = 4;
SELECT X.XML.EXTRACT('/*') AS Artist FROM XML X WHERE ID = 4;
SELECT XML FROM XML WHERE ID = 4;
Now let's examine how to extract individual pieces from within an XML
document. XML document subset parts are searched for and retrieved
using pattern-matching methods and various functions. Pattern-matching
methods are similar to regular expressions (see Chapter 14). An XML docu-
ment is effectively parsed for specific strings or tags and then the parts
within the matched patterns are returned. Various standard pattern-match-
ing characters are used for XML subset searches:
