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.
1b.
Define a Data Schema 
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="phonebook">
<xsd:complexType>
<xsd:sequence>
    <xsd:element maxOccurs="unbounded" minOccurs="1" name="department" type="deptType"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:complexType name="deptType">
    <xsd:sequence>
        <xsd:element name="deptname" type="xsd:string"/>
        <xsd:element name="deptphone" type="xsd:string"/>
        <xsd:element name="deptfax" type="xsd:string"/>
        <xsd:element name="deptemail" type="xsd:string"/>
        <xsd:element maxOccurs="unbounded" minOccurs="1" name="employee" type="empType"/>
    </xsd:sequence>
</xsd:complexType>
<xsd:complexType name="empType">
    <xsd:sequence>
        <xsd:element name="empid" type="xsd:integer"/>
        <xsd:element name="empfname" type="xsd:string"/>
        <xsd:element name="emplname" type="xsd:string"/>
        <xsd:element name="empphone" type="xsd:string"/>
        <xsd:element name="empemail" type="xsd:string"/>
    </xsd:sequence>
</xsd:complexType>
			</xsd:schema>
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>
1d.
Create an XSL file containing a stylesheet 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
	<title>Company phonebook </title> 
<body>
	<table border="2">
		<tr bgcolor="khaki">
			<th align="left">
				Department Name 
			</th>
			<th align="left">
				Department Phone 
			</th>
			<th align="left">
				Department Fax 
			</th>
			<th align="left">
				Department email 
			</th>
		</tr>
		<tr>
			<td align="left">
				<xsl:value-of select="//deptname" />
			</td>
			<td align="left">
				<xsl:value-of select="//deptphone" />
			</td>
			<td align="left">
				<xsl:value-of select="//deptfax" />
			</td>
			<td align="left">
				<xsl:value-of select="//deptemail" />
			</td>
		</tr>
	</table>
	<p />
	<table border="2">
		<tr bgcolor="beige">
			<th colspan="3">
				employees 
			</th>
		</tr>
		<tr bgcolor="beige">
			<th align="left" rowspan="5">
				employee Id 
			</th>
			<th align="left" rowspan="5">
				Firstname 
			</th>
			<th align="left" rowspan="5">
				Lastname 
			</th>
			<th align="left" rowspan="5">
				Phone 
			</th>
			<th align="left" rowspan="5">
				Email 
			</th>
		</tr>
		<xsl:for-each select=" phonebook /department/* ">
			<tr>
				<td align="left">
					<xsl:value-of select=".//empid" />
				</td>
				<td>
					<xsl:value-of select=".// empfname" />
				</td>
				<td align="center">
					<xsl:value-of select=".//emplname" />
				</td>
				<td align="left">
					<xsl:value-of select=".//empphone" />
				</td>
				<td align="left">
					<xsl:value-of select=".//empemail" />
				</td>
			</tr>
		</xsl:for-each>
	</table>
	</body>
	</html>
</xsl:template>
</xsl:stylesheet>
2.
Create a data schema for your university of college’s course bulletin. 
<?xml version="1.0" encoding="UTF-8"?>
	<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	    <xsd:element name="bulletin">
	        <xsd:complexType>
	            <xsd:sequence>
	                <xsd:element maxOccurs="unbounded" minOccurs="1" name="course" type="courseType"/>
	            </xsd:sequence>
	        </xsd:complexType>
	    </xsd:element>
	    <xsd:complexType name="courseType">
	        <xsd:sequence>
	            <xsd:element name="courseid" type="xsd:string"/>
	            <xsd:element name="coursename" type="xsd:string"/>
	            <xsd:element maxOccurs="unbounded" minOccurs="0" name="prereqid" type="xsd:string"/>
	            <xsd:element maxOccurs="unbounded" minOccurs="1" name="section" type="sectionType"/>
	        </xsd:sequence>
	    </xsd:complexType>
	    <xsd:complexType name="sectionType">
	        <xsd:sequence>
	            <xsd:element name="sectionid" type="xsd:integer"/>
	            <xsd:element name="semester" type="xsd:string"/>
	            <xsd:element name="room" type="xsd:integer"/>
	            <xsd:element name="time" type="xsd:time"/>
	            <xsd:element name="days" type="xsd:string"/>
	        </xsd:sequence>
	    </xsd:complexType>
	</xsd:schema>
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>
4.
Create a schema for a bus timetable.
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <!--Bus company -->
    <xsd:element name="busCompany">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="companyName" type="xsd:string"/>
                <xsd:element maxOccurs="unbounded" minOccurs="1" name="route" type="routeType"/>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
    <!--Route-->
    <xsd:complexType name="routeType">
        <xsd:sequence>
            <xsd:sequence>
                <xsd:element name="routeName" maxOccurs="1" minOccurs="0" type="xsd:string"/>
                <xsd:element name="routeColor" maxOccurs="1" minOccurs="0" type="xsd:string"/>
                <xsd:element name="routeNumber" maxOccurs="1" minOccurs="0" type="xsd:integer"/>
                <xsd:element maxOccurs="unbounded" minOccurs="1" name="stop" type="stopType"/>
                <xsd:element maxOccurs="unbounded" minOccurs="1" name="schedule" type="scheduleType"/>
            </xsd:sequence>
        </xsd:sequence>
    </xsd:complexType>
    <!--Stop-->
    <xsd:complexType name="stopType">
        <xsd:sequence>
            <xsd:element name="stopNumber" type="xsd:integer"/>
            <xsd:element name="stopName" maxOccurs="1" minOccurs="0" type="xsd:string"/>
            <xsd:element name="stopStreet"  type="xsd:string"/>
            <xsd:element name="stopCity" maxOccurs="1" minOccurs="0" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
    <!--Schedule-->
    <xsd:complexType name="scheduleType">
        <xsd:sequence>
            <xsd:element maxOccurs="unbounded" minOccurs="1" name="departure" type="stopTimeType"/>
        </xsd:sequence>
    </xsd:complexType>
    <!--Times-->
    <xsd:complexType name="stopTimeType">
        <xsd:sequence>
            <xsd:element name="stopNumber" type="xsd:integer"/>
            <xsd:element name="departureTime" type="xsd:time"/>
        </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
5b.
Who is the last customer in the file? 
/customers/customer[last()]/name
5c.
Select all customers in Sweden. 
/customers/customer[country="Sweden"]
5d.
List the payments of more than USD 100,000. 
/customers/customer/payment[amount > 100000]
5e.
Select the first payments by Toys4GrownUps.com.
/customers/customer[name= "Toys4GrownUps.com"]/payment[1]
5f.
What was the payment date for check DP677013?
//payment[check="DP677013"]/date
5g.
Who paid with check DP677013? 
//payment[check="DP677013"]/../name
5h.
What amounts were received on 2003-12-04? 
//payment[date="2003-12-04"]/amount
5i.
Who made payments on 2003-12-04? 
//payment[date="2003-12-04"]/../name
5j.
List the numbers of all checks from customers in Denmark.
/customers/customer[country="Denmark"]/payment/check
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
6b.
Who is the last customer in the file? 
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer[last()]/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
6d.
List the payments of more than USD 50,000.
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer/payment[amount > 100000]
6e.
Select the first payments by Toys4GrownUps.com.
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer[name= "Toys4GrownUps.com"]/payment[1]
6f.
What was the payment date for check DP677013?

doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[check="DP677013"]/date 







  

      
 6g. 

    

    Who paid with check DP677013?



  

doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[check="DP677013"]/../name







  

      
 6h. 

    

    What payments were received on 2003-12-04? 



  

doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[date="2003-12-04"]/amount







  

      
 6i. 

    

    Who made payments on 2003-12-04?

    
  
    
doc("http://richardtwatson.com/xml/customerpayments.xml")//payment[date="2003-12-04"]/../name
    






  

      
 6j. 

    

    List the numbers of all checks from customers in Denmark.

    
  
    
doc("http://richardtwatson.com/xml/customerpayments.xml")/customers/customer[country="Denmark"]/payment/check
    











  

    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:
  10-Dec-2021