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();
}