Using XmlPrime to access SQL databases

One of the uses of XQuery is to query relational databases. This posting is going to demonstrate how one might use XmlPrime to access a SQL database.

To access various data sources you can write a collection resolver implementing the ICollectionResolver interface. To do this you need to implement the following method:

public IXPathNavigable[] Resolve(AnyUri uri,
                                 DocumentSet documentSet,
                                 XmlNameTable nameTable,
                                 XsltWhitespaceSettings whitespaceSettings)

First thing we need is a URI format to indicate we are connecting to the database, for example:

mssql://username:password@server/database/table

We need to convert this URI to a SQL connection string, this can be done as follows:

var connectionString = new StringBuilder();
connectionString.Append("Server=" + uri.Host + ";");
var split = uri.Path.Split('/');
var table = split[2];
connectionString.Append("Database=" + split[1] + ";");
var userInfo = uri.UserInfoItems;
if (userInfo == null)
   connectionString.Append("Trusted_Connection=True;");
else
{
   connectionString.Append("User ID=" + userInfo[0] + 
                           ";Password="+userInfo[1] + ";");
}

We can now connected to the database and generate some XML nodes for our collection. The XML format is going to be:

<row column1="value1" column2="value2"/>
<row column1="value3" column2="value4"/>

We now open the database and get the data from the specified table:

using (var connection = new SqlConnection(connectionString.ToString()))
{
   connection.Open();
   var command = new SqlCommand("SELECT * FROM " + table, connection);
   var collection = new List<IXPathNavigable>();
   var doc = new XmlDocument(nameTable);
   using (var reader = command.ExecuteReader())
   {
      while (reader.Read())
      {
         ...
      }
   }
   return collection.ToArray();
}

The XML nodes need to be created with the same name table as the query is using, hence an XmlDocument is created with the name table passed into the Resolve method.

Next we need some code to put in the while loop to generate the XML elements for each row. A certain degree of mapping is required between SQL field types and XML types. This code shows a subset of possible field types for demonstration purposes.

var row = doc.CreateElement(table);
for(var i = 0;i<reader.FieldCount;i++) {
   if (!reader.IsDBNull(i))
   {
      var type = reader.GetFieldType(i);
      XPathAtomicValue value;
      if(type == typeof(Boolean))
         value = XPathAtomicValue.Create(reader.GetBoolean(i));
      else if(type = typoeof(Byte[])) {
         var length = reader.GetBytes(i, 0, null, 0, 0);
         var bytes = new byte[length];
         reader.GetBytes(i, 0, bytes, 0, (int)length);
         value = XPathAtomicValue.CreateBase64Binary(bytes);
      }
      else if (type == typeof(DateTime))
         value = XPathAtomicValue.Create(
                   new DateTimeWithZone(reader.GetDateTime(i), (TimeSpan?)null)
                 );
      else if (type == typeof(Int32))
         value = XPathAtomicValue.Create(reader.GetInt32(i));
      else if (type == typeof(String))
         value = XPathAtomicValue.Create(reader.GetString(i));
      else
         // TODO: similar methods will need to be implemented
         // for other SQL field types
         throw new NotImplementedException();
      var attr = d.CreateAttribute(reader.GetName(i));
      attr.Value = value.Value;
      row.Attributes.Append(attribute);
   }
}
collection.Add(row);

So the whole class will be:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Xml;
using System.Xml.XPath;

namespace xp.Connectors
{
   public class SimpleSqlCollectionResolver : ICollectionResolver
   {
      public IXPathNavigable[] Resolve(AnyUri uri,
                                 DocumentSet documentSet,
                                 XmlNameTable nameTable,
                                 XsltWhitespaceSettings whitespaceSettings)
      {
         if (uri != null && uri.Scheme == "mssql" && uri.Path != null)
         {
            var connectionString = new StringBuilder();
            connectionString.Append("Server=" + uri.Host + ";");
            var split = uri.Path.Split('/');
            var table = split[2];
            connectionString.Append("Database=" + split[1] + ";");
            var userInfo = uri.UserInfoItems;
            if (userInfo == null)
               connectionString.Append("Trusted_Connection=True;");
            else
            {
               connectionString.Append("User ID=" + userInfo[0] +
                                       ";Password="+userInfo[1] + ";");
            }

            using (var connection = new SqlConnection(connectionString.ToString()))
            {
               connection.Open();
               var command = new SqlCommand("SELECT * FROM " + table, connection);
               var collection = new List<IXPathNavigable>();
               var doc = new XmlDocument(nameTable);
               using (var reader = command.ExecuteReader())
               {
                  while (reader.Read())
                  {
                     var row = doc.CreateElement(table);
                     for (var i = 0; i < reader.FieldCount; i++)
                     {
                        if (!reader.IsDBNull(i))
                        {
                           var type = reader.GetFieldType(i);
                           XPathAtomicValue value;
                           if (type == typeof(Boolean))
                              value = XPathAtomicValue.Create(reader.GetBoolean(i));
                           else if (type == typeof(Byte[]))
                           {
                              var length = reader.GetBytes(i, 0, null, 0, 0);
                              var bytes = new byte[length];
                              reader.GetBytes(i, 0, bytes, 0, (int)length);
                              value = XPathAtomicValue.CreateBase64Binary(bytes);
                           }
                           else if (type == typeof(DateTime))
                              value = XPathAtomicValue.Create(new DateTimeWithZone(reader.GetDateTime(i), (TimeSpan?)null));
                           else if (type == typeof(Int32))
                              value = XPathAtomicValue.Create(reader.GetInt32(i));
                           else if (type == typeof(String))
                              value = XPathAtomicValue.Create(reader.GetString(i));
                           else
                              throw new NotImplementedException();
                           var attr = doc.CreateAttribute(reader.GetName(i));
                           attr.Value = value.Value;
                           row.Attributes.Append(attr);
                        }
                     }
                     collection.Add(row);
                  }
               }
               return collection.ToArray();
            }
         }
         return null;
      }
   }
}

This class can now be passed into a DocumentSet which can then be set on the DynamicContextSettings of a query.

var documentSet = new DocumentSet(nameTable,null,
                                       new SimpleSqlCollectionResolver(),
                                       null);
var dynamicSettings = new DynamicContextSettings {DocumentSet = documentSet};

We can then run queries accessing the database by using:

collection('mssql://SERVER/Northwind/Employees')

or if we are accessing multiple tables from the same database a nice syntax is

declare base-uri "mssql://SERVER/Northwind/";
collection('Employees')

Of course, the performance of this will be appalling compared to native SQL queries. In a future release of XmlPrime we aim to offer native SQL support with a number of the XQuery operations mapped onto SQL operations and run on the database, rather than loading whole tables as XML documents.

In another post we'll look at implementing ICollectionTypeResolver so that our query optimizer starts taking advantage of the field static type information when compiling the query.