| Team Fly | |
In this project, we will be querying for a specific XML document and then updating it.
1. Log into SQL*Plus.
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:
SQL> select extractvalue(value(c),'/customer/telephone')
2 from xcustomer c
3 where existsnode(value(c),'/customer/name = ''Chris Smith"') = 1
5. Again, when you see the SQL prompt, enter the forward slash (/) to execute it:
SQL> /
EXTRACTVALUE(VALUE(C),'/CUSTOMER/TELEPHONE')
------------------------------------------------
123 555-1234
6. Enter the following SQL query to change the telephone number to 888 555-1234:
update xcustomer c
set value(c) = updateXML(value(c), '/customer/telephone/text()','888 555-1234')
where existsnode(value(c),'/customer/name = "Chris Smith"') = 1
/
7. See if your data has changed by entering the following query.
SQL> select extract(value(c), 'customer')
2 from XCUSTOMER c
3 where existsnode(value(c),'/customer/name = " Chris Smith "') = 1
4 /
You should now see output similar to the following on your screen:
EXTRACT(VALUE(C),'CUSTOMER')
---------------------------------------
<customer>
<name>Chris Smith</name>
<address>116 Main street
Big City
U.S.A.</address>
<telephone>888 555-1234</telephone>
</customer>
| Team Fly | |