Banner

 

13 - XML

Answers to exercises

1.
A business has a telephone directory that records the first and last name, telephone number and email address of everyone working in the firm. Departments are the main organizing unit of the firm so the telephone directory is typically displayed in department order and shows for each department the contact phone and fax number and email address.
1a.
Create a hierarchical data model for this problem.
1c.
Create an XML file containing some directory data 
<?xml version="1.0" encoding="UTF-8"?>
<phonebook xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="phonebook.xsd">
    <department>
        <deptname>Sales</deptname>
        <deptphone>706.542.3700</deptphone>
        <deptfax>706.542.3743</deptfax>
        <deptemail>sales@yourbiz.com</deptemail>
        <employee>
            <empid>18890</empid>
            <empfname>James</empfname>
            <emplname>Smith</emplname>
            <empphone>706.542.3702</empphone>
            <empemail>jsmith@yourbix.com</empemail>
        </employee>
        <employee>
            <empid>18990</empid>
            <empfname>Joanna</empfname>
            <emplname>Smart</emplname>
            <empphone>706.542.3703</empphone>
            <empemail>jsmart@yourbix.com</empemail>
        </employee>
        <employee>
            <empid>18732</empid>
            <empfname>Mary</empfname>
            <emplname>Hunt</emplname>
            <empphone>706.542.3704</empphone>
            <empemail>mhunt@yourbix.com</empemail>
        </employee>
    </department>
</phonebook>
3.
Create a data schema for a credit card statement 
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="creditcard">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element maxOccurs="unbounded" minOccurs="1" name="card" type="cardType"/>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
    <xsd:complexType name="cardType">
        <xsd:sequence>
            <xsd:element name="cardnum" type="xsd:string"/>
            <xsd:element name="cardfname" type="xsd:string"/>
            <xsd:element name="cardlname" type="xsd:string"/>
            <xsd:element name="cardaddress" type="xsd:string"/>
            <xsd:element name="cardlimit" type="xsd:integer"/>
            <xsd:element maxOccurs="unbounded" minOccurs="0" name="prereqid" type="xsd:string"/>
            <xsd:element maxOccurs="unbounded" minOccurs="1" name="cardstatement" type="statementType"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="statementType">
        <xsd:sequence>
            <xsd:element name="statementid" type="xsd:integer"/>
            <xsd:element name="statementdate" type="xsd:date"/>
            <xsd:element maxOccurs="unbounded" minOccurs="1" name="statementcharge" type="chargeType"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="chargeType">
        <xsd:sequence>
            <xsd:element name="chargeid" type="xsd:string"/>
            <xsd:element name="chargeamount" type="xsd:decimal"/>
            <xsd:element name="chargedate" type="xsd:date"/>
            <xsd:element name="chargebusiness" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>
5.
Using the portion of ClassicModels that has been converted to XML, answer the following questions using XPath.
5a.
List all customers. 
/customers/customer/name
5c.
Select all customers in Sweden. 
/customers/customer[country="Sweden"]
5e.
Select the first payments by Toys4GrownUps.com.
/customers/customer[name= "Toys4GrownUps.com"]/payment[1]
5g.
Who paid with check DP677013? 
//payment[check="DP677013"]/../name
5i.
Who made payments on 2003-12-04? 
//payment[date="2003-12-04"]/../name
6.
Using the portion of ClassicModels that has been converted to XML, answer the following questions using XQuery.
6a.
List all customers.
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer/name
6c.
Select all customers in Sweden sorted by customer name.
for $customer in doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer[country="Sweden"]
order by $customer
return $customer
6e.
Select the first payments by Toys4GrownUps.com.
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer[name= "Toys4GrownUps.com"]/payment[1]
6g.
Who paid with check DP677013?
doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[check="DP677013"]/../name
6i.
Who made payments on 2003-12-04?
doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[date="2003-12-04"]/../name

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 02-Dec-2022