CSC513 X2

CSC513 X2: Store, retrieve, and query business messages

Announcement

4/21 helpful link for inserting large xml file into xml table
http://sivasakki.blogspot.com/2008/02/handling-more-than-4000-characters-in.html
4/26 Some students asked the "shipping to address" in the third requirement. It is meant to be the "shipTo" element under "orderLogisticalInformation" element.

Description

Business related information such as product orders, invoices, and customer relations are often stored in XML format. For the ease of management, XMLs are usually stored in XML DB. In this project, students have the chance to store XML(s) into XML DB and retrieve relevant information with SQL/XML from the XML DB.

First, follow instructions at http://www.csc.ncsu.edu/techsupport/technotes/oracle.php to access Oralce 10g database. The initial oracle account password is your nine digit student ID number.

The XML you are required to manipulate in this project is from Business Message Standards message packages and similar to the messages from X1. The Business Message Standards (BMS) provides the necessary information to implement XML message and extensions as part of the GS1 System. The Order business message specifies the sender, the receiver and detailed order information.

Requirement

Write SQL script to create a table named BusinessMessages with two columns: MessageID of Integer type, and Message of XML type.
Write SQL query to insert a BMS XML message "Order" (xml format, html representation) into the table BusinessMessages column Message with MessageID 1.
Write SQL/XML query to retrieve shipping to address from Order.
Write SQL/XML query to calculate the total tax paid in this order (sum of dutyFeeTaxAmount).
Write SQL/XML query to return the orderLineItems elements ordered by netAmount in descending order.

Deliverable

Put the scripts above in a SQL/XML file, name it to your_unity_id.sql, and submit in the submission locker.

Contact