Database Reference
In-Depth Information
FROM SONG S
WHERE S.ARTIST_ID = A.ARTIST_ID)
AS tSONG_LIST))).GETCLOBVAL()
AS ARTISTXML FROM ARTIST A;
Now let's look at how XML documents can be changed in an Oracle
database.
17.2.2
XML and the Database
In this section we examine XML and Oracle Database in three ways: (1)
creating new XML documents in the database; (2) retrieving XML docu-
ments stored in the database, both in whole and in part; and (3) changing
XML documents stored in the database.
17.2.2.1
New XML Documents
This command creates a table to store XML documents. This same table
creation command has already been shown earlier in this chapter but is
repeated here for convenience.
CREATE TABLE XML (ID NUMBER NOT NULL, XML XMLType
, CONSTRAINT XPK_XML PRIMARY KEY (ID));
There are various methods of adding XML data to a database. In short,
an XML document string can be added as a CLOB object, typecast as
XMLType datatype from a string, or added using XMLELEMENT and
similar SQL/XML functions. The XMLELEMENT function produces an
XMLType datatype. In this case, the query shown following is described by
the XML document shown in Figure 17.12. This INSERT command will
create an XMLType data object in the XML table just created.
INSERT INTO XML(ID,XML)
SELECT CD.MUSICCD_ID, XMLELEMENT("Artist"
, XMLATTRIBUTES(A.NAME "Name")
, 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"
