Database Reference
In-Depth Information
Figure 17.15
Demonstrating
EXTRACT and
EXTRACTVALUE.
SELECT DISTINCT(MUSICCD_ID) FROM CDTRACK WHERE SONG_ID IN
(SELECT SONG_ID FROM SONG
WHERE PLAYING_TIME IS NOT NULL);
The next example extracts every City tag and the value within every City
tag for all entries in the XML document. The result is shown in Figure
17.15.
COLUMN TAG FORMAT A32
COLUM CITY FORMAT A20
SELECT ID, EXTRACT(XML, '/Artist/City') AS Tag
, EXTRACTVALUE(XML, '/Artist/City') AS City
FROM XML;
The next two examples use EXTRACT to retrieve, EXISTSNODE to
validate and predicate pattern matching to find multiple elements. Results
are shown in Figures 17.16 and 17.17.
