XML & Relational Databases
by Andrew Conrad and Dare Obasanjo 

   
Listing One
<CustomerList>
    <Customer LastName="Jones" FirstName="Jeff">
        <WorkPhone>5556767</WorkPhone>
        <CellPhone>5558888</CellPhone>
        <HomePhone>5553232</HomePhone>
</Customer> 
<Customer LastName="Smith" FirstName="Jane">
        <CellPhone>5552444</CellPhone>
        <Pager>5558989</Pager>
</Customer>
</CustomerList>

Listing Two
<CustomerList>
<Customer LastName="Jones" FirstName="Jeff">
        <WorkPhone>5556767</WorkPhone>
        <CellPhone>5558888</CellPhone>
        <HomePhone>5553232</HomePhone>
        <Order Product="Apples" Quantity="25" >
            <Shipped>09/17/2001</Shipped>
        </Order>
</Customer> 
<Customer LastName="Smith" FirstName="Jane">
        <CellPhone>5552444</CellPhone>
        <Pager>5558989</Pager>
        <Order Product="Oranges" Quantity="25" >
            <Shipped>06/15/2001</Shipped>
        </Order>
        <Order Product="Tomatoes" Quantity="100" />
</Customer>
</CustomerList>

Listing Three
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes">
    <xsd:element name="CustomerList" >
        <xsd:complexType>
                <xsd:sequence>
                    <xsd:element ref="Customer" />
                </xsd:sequence>
            </xsd:complexType>
    </xsd:element>  
    <xsd:element name="Customer" >
        <xsd:complexType>
                <xsd:sequence>
                    <xsd:element name="Order" maxOccurs="unbounded" >
                        <xsd:complexType>
                            <xsd:attribute name="OrderID" type="xsd:string"/>
                            <xsd:attribute name="OrderDate" type="xsd:date"/>
                    </xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
                <xsd:attribute name="CustomerID" type="xsd:string"/>
                <xsd:attribute name="Company" type="xsd:string"/>
                <xsd:attribute name="Name" type="xsd:string"/>
                <xsd:attribute name="Title" type="xsd:string"/>
                <xsd:attribute name="City" type="xsd:string"/>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>


Listing Four
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:dt="urn:schemas-microsoft-com:datatypes"
            xmlns:msdata="urn:schemas-microsoft-com:mapping-schema" >
   <xsd:annotation>
            <xsd:appinfo>
                <msdata:relationship name="CustomerOrder"                
                parent="Customers"
                        parent-key="CustomerID"
                        child-key="CustomerID"
                        child="Orders" />
            </xsd:appinfo>
    </xsd:annotation>
    <xsd:element name="CustomerList" msdata:is-constant="true" >
        <xsd:complexType>
                <xsd:sequence>
                    <xsd:element ref="Customer" />
                </xsd:sequence>
            </xsd:complexType>
    </xsd:element>  

    <xsd:element name="Customer" msdata:relation = 
                          "Customers"  msdata:key-fields="CustomerID" >
        <xsd:complexType>
                <xsd:sequence>
                    <xsd:element name=
                     "Order" maxOccurs="unbounded" msdata:relation = "Orders" 
                    msdata:relationship="CustomerOrder" >
                        <xsd:complexType>
                        <xsd:attribute name="OrderID" msdata:field="OrderID"
                            type="xsd:string"/>
                            <xsd:attribute name=
                                        "OrderDate" msdata:field="OrderDate"
                            type="xsd:date"/>   
                    </xsd:complexType>
                    </xsd:element>
                </xsd:sequence>
                <xsd:attribute name="CustomerID" msdata:field=
                                          "CustomerId" type="xsd:string"/>
                <xsd:attribute name="Company" msdata:field=
                                           "CompanyName"  type="xsd:string"/>
                <xsd:attribute name="Name" msdata:field=
                                           "ContactName" type="xsd:string"/>
                <xsd:attribute name="Title" msdata:field=
                                           "ContactTitle" type="xsd:string"/>
                <xsd:attribute name="City" msdata:field=
                                           "City" type="xsd:string"/>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

Listing Five
using System;
using System.Collections;
using System.Xml;
using System.Xml.XPath;
using Microsoft.Data.SqlXml;
    public class CustomerDetails 
    {
static string NorthwindConnString = "Provider=SQLOLEDB;
          Server=SqlServerName;database=Northwind;Integrated Security=SSPI;";
        public static void Main(string[] args)
        {
        if(args.Length != 1){ 
            Console.WriteLine("Please specify the company name"); 
            return;
        } 
                // Get value from command line options
                string companyName = args[0];
                // Set up ADO.NET command to execute an xpath query and 
                // generate results using a mapping schema
                SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
                cmd.CommandText = "/CustomerList/Customer[@Company=
                                                     " + companyName + "]";
                cmd.CommandType = SqlXmlCommandType.XPath;
                // Wrap results in a root element so that we get back 
                // well-formed XML in case multiple customers from one
                // company found
                cmd.RootTag = "Customers";
                cmd.SchemaPath = "MappingSchema.xsd";
                // Get the results as a XmlReader
                XmlReader Reader;
                Reader = cmd.ExecuteXmlReader();
                // Put the results into a DOM
                XmlDocument doc = new XmlDocument(); 
                doc.Load(Reader);
                // print the name and title of each customer 
                //from the specified company 
                  foreach(XmlNode n in doc.DocumentElement.ChildNodes){
                    Console.WriteLine("NAME: {0}\nTITLE: {1}\n", 
                            n.Attributes["Name"].Value, 
                            n.Attributes["Title"].Value);
                  }
        }
}







3


