Team Fly

Page 287

16    </ShippingInstructions>
17    <SpecialInstructions>Air Mail</SpecialInstructions>
18    <LineItems>
19            <LineItem ItemNumber=''1">
20                    <Description>XML Done Easy</Description>
21                    <Part Id="37429155721" UnitPrice="29.95" Quantity="3"/>
22            </LineItem>
23            <LineItem ItemNumber="2">
24                    <Description>YATFG - A detailed explanation</Description>
25                    <Part Id="37429158920" UnitPrice="39.95" Quantity="3"/>
26            </LineItem>
27    </LineItems>
28* </PurchaseOrder>'))
SQL> /

Notice that line 4 in the preceding code defines the XML Schema associated to the XML document. If the XML schema http://xteoma.com/xsd/purchaseorder.xsd were not registered, did not exist on the path specified, or if the XML document did not match the constraints found, Oracle Database 10g would throw ORA-19007, described in the next listing:

ORA-19007: Schema and element do not match

Project 8-2 Storing XML

In this project, we will be creating a table of type XMLType and storing an XML document using a SQL query in the Oracle Database 10g.

Step by Step

1. Log into SQL*Plus using 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 statement:

      SQL> create table xcustomer of xmltype

5. You should now see the following output on your screen:

      SQL> /
      Table created.

6. Enter the following SQL Query to load XML data:

Team Fly