(c) 2003 Visual Studio Magazine 
Fawcette Technical Publications

XML	Store Book Data
Listing 1	The books.xml document contains several book titles along with their publication dates, authors, and publishers. You'll can execute an XQuery statement against this data to select titles based on criteria such as publisher and/or publication date.

<?xml version="1.0" encoding="utf-8" ?>
<books>
	<book year="2000">
		<title>Data on the Web</title>
		<author><last>Abiteboul</last><first>Serge
			</first></author>
		<author><last>Buneman</last><first>Peter
			</first></author>
		<author><last>Suciu</last><first>Dan
			</first></author>
		<publisher>Morgan Kaufmann 
			Publishers</publisher>
		<price>39.95</price>
	</book> 
	<book year="2001">
		<title>XML for ASP.NET Developers</title>
		<author><last>Wahlin</last><first>Dan
			</first></author>
		<publisher>Sams Publishing</publisher>
		<price>39.99</price>		
		</book>		
	<book year="2001">
		<title>ASP.NET: Tips, Tutorials, and 
			Code</title>
		<author><last>Mitchell</last><first>Scott
			</first></author>
		<author><last>Mack</last><first>Donny
			</first></author>
		<author><last>Walther</last><first>Stephen
			</first></author>
		<author><last>Seven</last><first>Doug</first></author>
		<author><last>Anders</last><first>Bill
			</first></author>
		<author><last>Nathan</last><first>Adam
			</first></author>
		<author><last>Payne</last><first>Chris
			</first></author>
		<author><last>Wahlin</last><first>Dan
			</first></author>
		<publisher>Sams Publishing</publisher>
		<price>49.95</price>	
	</book>
</books>

C#	Query Multiple Documents
Listing 2	The XQueryProcessor class provides a compilation and execution environment for XQuery statements. This allows you to run powerful queries against multiple XML documents, much as you can run SQL queries against relational database tables.

//Load query shown in Listing 3
StreamReader r = 
  new StreamReader(
	"../../Queries/BooksAuthors.xquery");

//Create XQuery processor
XQueryProcessor xp = new XQueryProcessor();

//Compile statement
xp.Compile(r.ReadToEnd());
r.Close();

//Define path to context documents and give alias
XmlDataSourceResolver ds = 
  new XmlDataSourceResolver(); 
ds.Add("BooksDoc",   "../../XML/Books.xml"); 
ds.Add("AuthorsDoc", "../../XML/Authors.xml");

//Perform query and write out results
StringWriter sw = new StringWriter();
xp.Execute(ds, sw);
Console.Write(sw.ToString());

XSD	Define Data Structure
Listing 3 	This XML schema defines what the structure of the XML data should be when the XmlAdapter fills the XPathDocument2 object. The main mapping schema references this XML schema and ties it with the database schema (see Listing 4).

<?xml version="1.0" encoding="utf-8" ?> 
<xsd:schema id="CustomerOrders" 
	elementFormDefault="qualified" 
	xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="Customer">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="Order" >
					<xsd:complexType>
					<xsd:attribute name="id" 
						type="xsd:string"/>
					<xsd:attribute name="orderID" 
						type="xsd:string"/>
					<xsd:attribute name="date" 
						type="xsd:date"/> 
			</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
			<xsd:attribute name="id" 
				type="xsd:string"/>
			<xsd:attribute name="name" 
				type="xsd:string" />
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

XML	Create a Database Schema
Listing 4	 This database schema document defines the structure of the Northwind database's Customers and Orders tables. The main mapping schema references this schema and ties it with the XML schema in Listing 3 (see Listing 5).

<?xml version="1.0" encoding="utf-8"?>
<y0:Database 
	xmlns:y0="http://schemas.microsoft.com/data/20
		02/09/28/rsd" 
	Name="Northwind">
		<y0:Schema Name="dbo">
			<y0:Tables>
				<y0:Table Name="Customers">
					<y0:Columns>
						<y0:Column Name="CustomerID" 
							SqlType="nchar" 
							Length="5"/>
						<y0:Column Name="CompanyName" 
							SqlType="nvarchar" 
							Length="40"/>
						<y0:Column AllowDbNull="true" 
							Name="ContactName" 
							SqlType="nvarchar" 
								Length="30"/>

				<!--Columns omitted for brevity -->

						</y0:Columns>
						<y0:Constraints>
							<y0:PrimaryKey 
								Name="PK_Customers">
								<y0:ColumnRef 
									Name="CustomerID"/>
							</y0:PrimaryKey>
						</y0:Constraints>
					</y0:Table>
				<y0:Table Name="Orders">
					<y0:Columns>
						<y0:Column Name=
							"OrderID" 
							AutoIncrement=
							"true" SqlType="int"/>
					<y0:Column Name="CustomerID" 
						AllowDbNull="true" 
						SqlType="nchar" Length="5"/>
					<y0:Column Name="EmployeeID" 
						AllowDbNull="true" 
						SqlType="int"/>

	 <!--Columns omitted for brevity -->

					</y0:Columns>
					<y0:Constraints>
						<y0:PrimaryKey 
							Name="PK_Orders">
							<y0:ColumnRef 
								Name="OrderID"/>
						</y0:PrimaryKey>
						<y0:ForeignKey 
							ForeignTable="Customers" 
							Name=
								"FK_Orders_Customers">
							<y0:ColumnMatch 
								ForeignName=
								"CustomerID" 
								Name="CustomerID"/>
							</y0:ForeignKey>
					</y0:Constraints>
			</y0:Table>
		</y0:Tables>
	</y0:Schema>
</y0:Database>

XML	Create an XML View
Listing 5 	The mapping schema ties together the XML schema in Listing 3 and the database schema in Listing 4. It has special elements that allow you to define mappings between database columns and XML elements or attributes. The mappings defined in this XML View are passed to the XQueryProcessor, which the XmlAdapter uses to fill an XPathDocument2 object with data (see Listing 6).

<?xml version="1.0" encoding="utf-8" ?> 
<m:MappingSchema 
	xmlns:m="http://schemas.microsoft.com/data/
		2002/09/28/mapping">
	<m:DataSources>
		<m:DataSource Name="XML" Direction="Target" 
			Type="Xml">
			<m:Schema Location=
				"CustomersOrders.xsd" />
		</m:DataSource>
		<m:DataSource Name="SQL-Northwind" 
			Direction="Source" Type="SQL Server">
		<m:Schema Location=
			"CustomersOrdersDB.xml" />
		<m:Variable Name="Orders_B" Select=
			"Orders" />
		<m:Variable Name=
				"Customers_A" Select="Customers"/>
		<m:Relationship Name="CustomersOrders" 
			FromVariable="Customers_A" 
				ToVariable="Orders_B">
			<m:FieldJoin From="CustomerID" 
				To="CustomerID" />
			</m:Relationship>
		</m:DataSource>
	</m:DataSources>
	<m:Mappings>
		<m:Map SourceVariable="Customers_A" 
		TargetSelect="/Customer">
			<m:FieldMap 
				SourceField="CustomerID" 
					TargetField="@id" />
			<m:FieldMap 
				SourceField="ContactName" 
					TargetField="@name" />
		</m:Map>
		<m:Map SourceVariable="Orders_B" 
			TargetSelect="/Customer/Order">
			<m:FieldMap 
				SourceField="OrderID" 
					TargetField="@orderID" />
			<m:FieldMap 
				SourceField="OrderDate" 
					TargetField="@date" />
			<m:FieldMap 
				SourceField="CustomerID" 
					TargetField="@id" />
		</m:Map>
	</m:Mappings>
</m:MappingSchema>

C#	Query Relational Data
Listing 6	You can assign the XQueryProcessor class an XML View mapping file through its XmlViewSchemaDictionary property. Once you assign the mapping file and open the database connection, the XmlAdapter can use the XQueryProcessor object to execute an XQuery statement and fill an XPathDocument2 object with XML data.

SqlConnection conn = null;
try {
	string query = 
		"declare namespace " +
		"map='http://schemas.microsoft.com" +
		"/xml/2002/09/28/mappingfunctions' " +
		"map:view('CustomersOrders')/" +
       "Customer[@id='ALFKI']";
	//Create XQueryProcessor so we can query SQL 
	//Server
	XQueryProcessor xquery = 
     new XQueryProcessor();
	MappingSchema map = new 
		MappingSchema("../../XmlAdapter/Queries/" +
		"CustomersOrdersMapping.xml");
	xquery.XmlViewSchemaDictionary.Add(
		"CustomersOrders",map);
	xquery.Compile(query);
	conn = new 
		SqlConnection("server=localhost;uid=sa; " +
		"pwd=password;database=northwind");
	conn.Open();
	XmlDataSourceResolver r = 
     new XmlDataSourceResolver();
	r.Add("SQL-Northwind", conn);
	XmlAdapter adapter = new XmlAdapter(resolver);
	XPathDocument2 doc = new XPathDocument2();
	adapter.Fill(doc, xquery.XmlCommand);
	StringWriter sw = new StringWriter();
	doc.Save(sw);
	Console.WriteLine(sw.ToString());
}
catch (Exception exp) {
	Console.WriteLine(exp.ToString());
}
finally {
	if (conn != null && 
		conn.State != ConnectionState.Closed) 
			conn.Close();
}

