Team Fly

Page 281

The following is the expected result (partial) of our SQL query:

<ProdCategory category=''Electronics">
 <product>
  <name>Y Box</name>
 </product>
 <product>
  <name>Home Theatre Package with DVD-Audio/Video Play</name>
 </product>
...
</ProdCategory>
<ProdCategory category="Hardware">
 <product>
   <name>Envoy 256MB - 40GB</name>
 </product>
 <product>
  <name>Envoy Ambassador</name>
</product>

Now, wasn't that fun? The best way to see how SQLX works is to try it yourself—meaning it's time for our first project!

Project 8-1 Creating an XML Listing

In this project, we will be creating an XML listing using a SQL query.

Step by Step

1. Log into SQL*Plus with the username/password sh/sh.

2. At the SQL> prompt, enter the long command, set long 10000, and then press ENTER.

3. At the SQL> prompt, enter the long command, set pagesize 80, and then press ENTER.

4. Enter the following SQL query (when you press ENTER at the end of the first line, Oracle Database 10g places the number 2> there, and so on, each time you start a new line):

      SQL> select xmlelement( "Country",
         2       xmlattributes(c.country_region as "Region"),
         3       xmlagg(xmlelement("Country",
         4               xmlforest(c.country_name as "Name"))))
         5  from countries c
         6  where c.country_region_id = 52801
         7  group by c.country_region

Team Fly