The power of XmlResolver

Finally I got a time to fully implement support for XmlResolver in XInclude.NET (see Extending XInclude.NET). Wow, this stuff looks so powerful! A friend of mine is writing an article about using resolvers in System.Xml, so no spoilers here, all I wanted is to illustrate what can be done now using XInclude.NET and custom XmlResolver.

So, somebody wants to include a list of Northwind employees into a report XML document. Yeah, directly from SQL Server database. Here comes XInclude.NET solution: custom XmlResolver, which queries database and returns XmlReader (via SQLXML of course).

report.xml:

<report>
  <p>Northwind employees:</p>
  <xi:include 
href="sqlxml://LOCO055/Northwind?query=
SELECT FirstName, LastName FROM Employees FOR XML AUTO"
xmlns:xi="http://www.w3.org/2001/XInclude"/>
</report>

sqlxml:// URI schema is a proprietary schema, supported by my custom XmlResolver. LOCO055 is my SQL Server machine name, Northwind is the database I want to query and query is the query.
Here goes SqlXmlResolver class:

public class SqlXmlResolver : XmlUrlResolver {
  static string NorthwindConnString = 
    "Provider=SQLOLEDB;Server={0};
     database={1};Integrated Security=SSPI";
  public override object GetEntity(Uri absoluteUri, 
          string role, Type ofObjectToReturn) {
    if (absoluteUri.Scheme == "sqlxml") {
      //Extract server and database names from the URI
      SqlXmlCommand cmd = 
        new SqlXmlCommand(string.Format(NorthwindConnString, 
        absoluteUri.Host, absoluteUri.LocalPath.Substring(1)));
      cmd.RootTag = "EmployeesList";
      //Extract SQL statement from the URI
      cmd.CommandText = 
        absoluteUri.Query.Split('=')[1].Replace("%20", " ");
      return cmd.ExecuteXmlReader();
    } else
      return base.GetEntity(absoluteUri, role, ofObjectToReturn);
    }
  }
}

Not really a sophisticated one, just checks if the URI schema is sqlxml:// and then extracts the data from the URI and runs the query via SQLXML plumbing. Then we can read report.xml via XIncludingReader:

XIncludingReader reader = new XIncludingReader("report.xml");
reader.XmlResolver = new SqlXmlResolver();
XPathDocument doc = new XPathDocument(reader);
...

And finally the result is:

<report>
  <p>Northwind employees:</p>
  <EmployeesList>
    <Employees FirstName="Nancy" LastName="Davolio"/>
    <Employees FirstName="Andrew" LastName="Fuller"/>
    <Employees FirstName="Janet" LastName="Leverling"/>
    <Employees FirstName="Margaret" LastName="Peacock"/>
    <Employees FirstName="Steven" LastName="Buchanan"/>
    <Employees FirstName="Michael" LastName="Suyama"/>
    <Employees FirstName="Robert" LastName="King"/>
    <Employees FirstName="Laura" LastName="Callahan"/>
    <Employees FirstName="Anne" LastName="Dodsworth"/>
  </EmployeesList>
</report>

That magic is supported by XInclude.NET version 1.2, which I'm going to release right now. Well, actually I don't think including SQL into URI was a good idea, but bear in mind, that's just a dummy sample to illustrate the power of XmlResolvers. Enjoy!