 | The AirlineBrokers database will be used to study XML data access. This database can be created using the SqlServer Enterprise Manager and the airlinebroker.sql script found in the Databases directory of this chapter. The AirlineBrokers database represents another service that the Acme reservation system uses. Acme customers can make airline reservations to the places they wish to go. | The database has several tables: -
Airlines: information about the various airlines in the database. -
PlaneType: the various planes that the airlines use. -
Flights: information about the various airlines' flights . -
Customers: information about customers. -
Reservations: information about the customers' reservations. Although in real life the Airline Broker and the Hotel Broker would not have the same Customers table, for simplicity we use the same table structure, and we use the same component to access it. DataSet and XML To illustrate the relationship between the relation model of the DataSet and the XML model, we will first fetch some information from the database. The DataSetXml example uses the same commands and techniques we have studied in this chapter to extract the data. First, the connection, DataSet , and the SqlDataAdapters for the various tables are created.  Dim connectString As String = _ "server=localhost;uid=sa;pwd=;database=AirlineBroker" Dim conn As New SqlConnection(connectString) Dim d As New DataSet("AirlineBroker") Dim airlinesAdapter As New SqlDataAdapter() Dim flightsAdapter As New SqlDataAdapter() Dim planetypeAdapter As New SqlDataAdapter() Dim customersAdapter As New SqlDataAdapter() Dim reservationsAdapter As New SqlDataAdapter() Then the various select commands to fetch the data are created, and the dataset is filled with the data from those tables: airlinesAdapter.SelectCommand = New SqlCommand(_ "select * from Airlines", conn) airlinesAdapter.Fill(d, "Airlines") flightsAdapter.SelectCommand = New SqlCommand(_ "select * from Flights", conn) flightsAdapter.Fill(d, "Flights") planetypeAdapter.SelectCommand = New SqlCommand(_ "select * from PlaneType", conn) planetypeAdapter.Fill(d, "PlaneType") customersAdapter.SelectCommand = New SqlCommand(_ "select * from Customers", conn) customersAdapter.Fill(d, "Customers") reservationsAdapter.SelectCommand = New SqlCommand(_ "select * from Reservations", conn) reservationsAdapter.Fill(d, "Reservations") We now have the data for the Airlines, Flights, PlaneType, Customers, and Reservations tables in the dataset. Next , we have the DataSet written out as an XML schema, the schema it infers from the data. Then the DataSet writes out the data as XML. d.WriteXmlSchema("Airlines.xsd") d.WriteXml("Airlines.xml") Here is some of the data that was written to the file Airlines.xml . The main element is Airline Broker, which was the name of the DataSet . Elements at the next lower level correspond to the various tables that were added to the database: Airlines , Flights , PlaneType , and Customers . There were no reservations in the database. There is one set for each row in the table. The elements under each of these tables correspond to the fields for that particular row. <AirlineBroker> <Airlines> <Name>America West</Name> <Abbreviation>AW</Abbreviation> <WebSite>www.americawest.com</WebSite> <ReservationNumber>555-555-1212</ReservationNumber> </Airlines> <Airlines> <Name>Delta</Name> <Abbreviation>DL</Abbreviation> <WebSite>www.delta.com</WebSite> <ReservationNumber>800-456-7890</ReservationNumber> </Airlines> ... <Flights> <Airline>DL</Airline> <FlightNumber>987</FlightNumber> <StartCity>Atlanta</StartCity> <EndCity>New Orleans</EndCity> <Departure>2001-10-05T20:15:00.0000000-04:00 </Departure> <Arrival>2001-10-05T22:30:00.0000000-04:00</Arrival> <PlaneType>737</PlaneType> <FirstCost>1300</FirstCost> <BusinessCost>0</BusinessCost> <EconomyCost>450</EconomyCost> </Flights> ... <Flights> <PlaneType> <PlaneType>737</PlaneType> <FirstClass>10</FirstClass> <BusinessClass>0</BusinessClass> <EconomyClass>200</EconomyClass> </PlaneType> ... <Customers> <LastName>Adams</LastName> <FirstName>John</FirstName> <EmailAddress>adams@presidents.org</EmailAddress> <CustomerId>1</CustomerId> </Customers> </AirlineBroker> From the structure of the data, the DataSet deduces a schema that was written to Airlines.xsd . We discuss here an excerpt from that file. There are no relationships or primary keys defined between any of the tables, such as Airlines and Flights , as in the database, because none were defined in the DataSet . If you look at the actual generated file, you will see that schema information was inferred for Reservations even though there was no data in the table. The schema preamble in the first line, reproduced here, defines the name of the schema as AirlineBroker, and we are using two namespaces in this schema document. One, abbreviated xsd , contains the XML Schema standard definitions. The other, abbreviated msdata , contains Microsoft definitions. ... <xsd:schema id="AirlineBroker" targetNamespace="" xmlns="" xmlns:xsd=http://www.w3.org/2001/XMLSchema xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"> The next line defines an element called AirlineBroker, which has an attribute that indicates this schema came from a DataSet . That is a Microsoft-defined attribute, not one defined by the W3C Schema namespace. This element AirlineBroker is a complex type, which means it is a structure composed of other types. This structure can have an unlimited number of any (or even none) of the types defined in the rest of the schema. <xsd:element name="AirlineBroker" msdata:IsDataSet="true"> <xsd:complexType> <xsd:choice maxOccurs="unbounded"> The Airlines element is defined next. It, too, is a structure, or complex type, whose elements, if present, appear in the structure in the order in which they were defined. Those elements, which correspond to the columns in the database table, are all defined to be strings that are optional. No primary keys were defined, and these strings are certainly not optional in the database, but that was what the DataSet deduced from the set of tables, constraints, and relationships currently defined in the DataSet . <xsd:element name="Airlines"> <xsd:complexType> <xsd:sequence> <xsd:element name="Name" type="xsd:string" minOccurs="0" /> <xsd:element name="Abbreviation" type="xsd:string" minOccurs="0" /> <xsd:element name="WebSite" type="xsd:string" minOccurs="0" /> <xsd:element name="ReservationNumber" type="xsd:string" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> The table Flights is defined similarly to Airlines. In addition to there being no primary key here, there is no foreign key defined for Airline or PlaneType. <xsd:element name="Flights"> <xsd:complexType> <xsd:sequence> <xsd:element name="Airline" type="xsd:string" minOccurs="0" /> <xsd:element name="FlightNumber" type="xsd:int" minOccurs="0" /> <xsd:element name="StartCity" type="xsd:string" minOccurs="0" /> <xsd:element name="EndCity" type="xsd:string" minOccurs="0" /> <xsd:element name="Departure" type="xsd:dateTime" minOccurs="0" /> <xsd:element name="Arrival" type="xsd:dateTime" minOccurs="0" /> <xsd:element name="PlaneType" type="xsd:string" minOccurs="0" /> <xsd:element name="FirstCost" type="xsd:decimal" minOccurs="0" /> <xsd:element name="BusinessCost" type="xsd:decimal" minOccurs="0" /> <xsd:element name="EconomyCost" type="xsd:decimal" minOccurs="0" /> </xsd:sequence> </xsd:complexType> </xsd:element> ... </xsd:choice> </xsd:complexType> </xsd:element> </xsd:schema> We will come back to this schema definition, but for the moment, let us continue to work with this example. Creating an XML Doc from a DataSet We create a new XML document from the DataSet . Using an XPath query to get the top of the document, we set up an XmlNodeReader to read through it. We can then print out the contents of the document to the console. The XmlNodeReader class knows how to navigate through the document. Dim xmldoc As New XmlDataDocument(d) Dim xmlreader As XmlNodeReader Try Dim node As XmlNode = xmldoc.SelectSingleNode("/") xmlreader = New XmlNodeReader(node) FormatXml(xmlreader) Catch e As Exception Console.WriteLine("Exception: {0}", e.ToString()) Finally If Not xmlreader Is Nothing Then xmlreader.Close() End If End Try ... Sub FormatXml(ByVal reader As XmlReader) Do While reader.Read() Select Case reader.NodeType ... Case XmlNodeType.Element Format(reader, "Element") Do While reader.MoveToNextAttribute() Format(reader, "Attribute") Loop Case XmlNodeType.Text Format(reader, "Text") Case XmlNodeType.Whitespace Format(reader, "Whitespace") End Select Loop End Sub Sub Format(ByVal reader As XmlReader, _ ByVal nodeType As String) Static lastNodeType As String = "" If nodeType = "Element" Then If lastNodeType = "Element" Then Console.WriteLine() End If Dim i As Integer For i = 0 To reader.Depth - 1 Console.Write(" ") Next Console.Write(reader.Name) ElseIf nodeType = "Text" Then Console.WriteLine("={0}", reader.Value) Else Console.Write(nodeType & "<" & reader.Name & _ ">" & reader.Value) Console.WriteLine() End If lastNodeType = nodeType End Sub The results resemble the XML that the DataSet wrote to a file. AirlineBroker Airlines Name=America West Abbreviation=AW WebSite=www.americawest.com ReservationNumber=555-555-1212 Airlines Name=Delta Abbreviation=DL WebSite=www.delta.com ReservationNumber=800-456-7890 Airlines Name=Northwest Abbreviation=NW WebSite=www.northwest.com ReservationNumber=888-111-2222 Airlines Name=Piedmont Abbreviation=P WebSite=www.piedmont.com ReservationNumber=888-222-333 Airlines Name=Southwest Abbreviation=S WebSite=www.southwest.com ReservationNumber=1-800-111-222 Airlines Name=United Abbreviation=UAL WebSite=www.ual.com ReservationNumber=800-123-4568 Flights Airline=DL FlightNumber=987 StartCity=Atlanta EndCity=New Orleans Departure=2001-10-05T20:15:00.0000000-04:00 Arrival=2001-10-05T22:30:00.0000000-04:00 PlaneType=737 FirstCost=1300 BusinessCost=0 EconomyCost=450 Flights Airline=UAL FlightNumber=54 StartCity=Boston EndCity=Los Angeles Departure=2001-10-01T10:00:00.0000000-04:00 Arrival=2001-10-01T13:00:00.0000000-04:00 PlaneType=767 FirstCost=1500 BusinessCost=1000 EconomyCost=300 PlaneType PlaneType=737 FirstClass=10 BusinessClass=0 EconomyClass=200 PlaneType PlaneType=767 FirstClass=10 BusinessClass=30 EconomyClass=300 Customers LastName=Adams FirstName=John EmailAddress=adams@presidents.org CustomerId=1 | 
