Using XML Serialization with SQL's FOR XML PATH
Describes a method for loading data objects using SQL Server 2005's new FOR XML PATH mode and XML serialization.
Introduction
Loading model objects with data from database has long been a complicated and tedious process. The more complex the model structure, the more complicated and resource intensive the issue becomes. SQL Server 2000 introduced the FOR XML clause, but to get an XML structure the way you want requires using cryptic syntax with the Explicit mode. And again, the more complex the structure, the worse the query becomes.
SQL Server 2005 introduced the FOR XML Path mode, allowing you to specify XML structure much more intuitively, using aliases that look more like XPath expressions. Armed with a reasonably simple way to specify XML output structure, loading graphs of objects through XML serialization greatly simplifies the process of selecting complex data.
In this article, I will show how this can be done with a simple example. The demo has two classes for getting information into the model. One is using an older approach of hand writing code to load objects, and one is using a FOR XML Path Stored Procedure and XML serialization. Because this project is dependent on a database, I haven't included a pre-built demo. To run the demo, you'll have to setup a database, so you can't just download it and run it anyway.
What You Need
To use this approach, you'll need your model to be XML serializable. For a refresher on doing that, see my article on Using the XmlSerializer Attributes. Once that's done, you will also need a SQL query or a Stored Procedure that uses FOR XML to produce XML that mirrors your model's XML structure. Then, all you need is code to run the query and deserialize the XML output (like the SqlXmlData class included in the demo project).
The Demo Project
The demo project includes a SimpleCMS.sql SQL file for creating the demo database. It creates the database, creates two tables, creates a Stored Procedure, and inserts some sample data. The demo app is designed to select a company and all its associated contacts for displaying in a TreeView. It's just designed as a proof of concept to show how this approach can be used to select complex data.
The database has two tables, Company and Contact.
The model is similar to the database structure, but is a little more complicated.
Old School
The demo project includes a class called SqlRawData that fills a Companies model object the hard way.
SqlRawData does the following to get information for a company and its associated contacts:
public CompanyList GetData(int id) {
if (dbs == null || conn == null) {
throw new ApplicationException("Data connection not yet initialized");
}
CompanyList obj = new CompanyList();
obj.Companies = new List<Company>();
using (IDbCommand cmd = conn.CreateCommand()) {
cmd.CommandText = GetCompanySQL();
cmd.Parameters.Add(new SqlParameter("@CompanyId", id));
conn.Open();
using (IDataReader idr = cmd.ExecuteReader()) {
if (idr.Read()) {
Company cmp = ReadCompany(idr);
if (cmp != null) { obj.Companies.Add(cmp); }
}
idr.Close();
}
}
foreach (Company cmp in obj.Companies) {
cmp.Contacts = new List<Contact>();
using (IDbCommand cmd = conn.CreateCommand()) {
cmd.CommandText = GetContactSQL();
cmd.Parameters.Add(new SqlParameter("@CompanyId", cmp.Id));
using (IDataReader idr = cmd.ExecuteReader()) {
while (idr.Read()) {
Contact cnt = ReadContact(idr);
if (cnt != null) { cmp.Contacts.Add(cnt); }
}
}
}
}
conn.Close();
return obj;
}
private Company ReadCompany(IDataReader idr) {
/*
0 Cmp_id 1 Name 2 Address
3 City 4 State 5 Zip
*/
Company cmp = new Company();
cmp.Id = Convert.ToInt32(idr[0]);
cmp.Name = idr.GetString(1);
cmp.Address = new Address();
cmp.Address.Street = idr.GetString(2);
cmp.Address.City = idr.GetString(3);
cmp.Address.State = idr.GetString(4);
cmp.Address.Zip = idr.GetString(5);
return cmp;
}
private Contact ReadContact(IDataReader idr) {
/*
0 Cnt_id 1 [Name] 2 Email
3 Phone 4 UserName 5 Password
*/
Contact cnt = new Contact();
cnt.Id = Convert.ToInt32(idr[0]);
cnt.Name = idr.GetString(1);
cnt.ContactInfo = new ContactInfo();
cnt.ContactInfo.Email = idr.GetString(2);
cnt.ContactInfo.Phone = idr.GetString(3);
cnt.Login = new Login();
cnt.Login.Uid = idr.GetString(4);
cnt.Login.Pwd = idr.GetString(5);
return cnt;
}
private string GetCompanySQL() {
StringBuilder sql = new StringBuilder();
return sql.ToString();
}
private string GetContactSQL() {
StringBuilder sql = new StringBuilder();
return sql.ToString();
}
This is a little long winded due to the fact that I chose to use direct SQL instead of Stored Procedures, but there is still a considerable amount of code that has to be written to do the separate queries and load each object type by hand. (The GetXXSQL() methods have been truncated for displaying the example here. See the source download for the full source.)
As you can see, I have to first query Company data, and I chose to pass the IDataReader off to another method to build the object. Then, I have to do the same for Contacts and add them to the Company as they are created. This method is fine, but the more complex the data structure and the deeper the nesting, the more complicated the code becomes. This also requires more and more code to accommodate more complex structures. Plus, this requires multiple trips to the database. In this example, you could avoid multiple trips, but with more complexity, you could very easily get into a situation where you could no longer do it with a single query.
FOR XML Path
Using SQL Server 2005's FOR XML Path, you can accomplish the same result with a single query and far less code. The SqlXmlData class in the demo project has several generic methods for serializing and deserializing XML. It also includes the following method that accepts a SQL statement or Stored Pocedure name, a CommandType, and an array of parameters. This method runs the query (assuming it uses FOR XML with the correct structure), and deserializes the result to the type generically supplied.
public T GetData<T>(string sql, CommandType cmdtype, params SqlParameter[] parameters) {
if (dbs == null || conn == null) {
throw new ApplicationException("Data connection not yet initialized");
}
T obj = default(T);
string xml = null;
using (IDbCommand cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
cmd.CommandType = cmdtype;
foreach (SqlParameter idp in parameters) {
cmd.Parameters.Add(idp);
}
conn.Open();
using (IDataReader idr = cmd.ExecuteReader()) {
if (idr.Read()) {
xml = idr[0].ToString();
}
idr.Close();
}
conn.Close();
}
if (xml != null) {
obj = Deserialize<T>(xml);
}
return obj;
}
Using this class, the only code you have to write is something like this:
private CompanyList GetCompany(int id) {
CompanyList list = null;
using (SqlXmlData data = new SqlXmlData(
"Server=localhost;Database=SimpleCMS;Trusted_Connection=True;"
)) {
list = data.GetData<CompanyList>("spGetCompanyXML",
CommandType.StoredProcedure,
new SqlParameter("@CompanyId", id)
);
}
return list;
}
The Stored Procedure
By giving fields in your query an XPath-like mapping, you can specify how the output XML will be structured. You specify attributes like "@a", elements like "e", sub-elements like "e1/e2", text like "text()", and data values like "data()". Sub structures can be specified by a sub query that also uses FOR XML. By including root('name') in the FOR XML clause, you can encapsulate your structure inside another node. The spGetCompanyXML Stored Procedure looks like this:
select
Company.Cmp_id "@id",
Company.[Name] "@name",
Company.Address "Address/Street",
Company.City "Address/City",
Company.State "Address/State",
Company.Zip "Address/Zip",
(
select
Contact.Cnt_id "@id",
Contact.[Name] "@name",
Contact.Email "ContactInfo/@email",
Contact.Phone "ContactInfo/@phone",
Contact.UserName "Login/@uid",
Contact.Password "Login/@pwd"
from
Contact
where
Contact.Cmp_id = @CompanyId
order by
Contact.[Name]
for xml path ('Contact'), root('Contacts'), type
)
from
Company
where
Company.Cmp_id = @CompanyId
for xml path ('Company'), root('Companies'), type
This Stored Procedure will produce output like this:
<Companies>
<Company id="1" name="Company A">
<Address>
<Street>101 A St.</Street>
<City>Hays</City>
<State>Kansas</State>
<Zip>67601</Zip>
</Address>
<Contacts>
<Contact id="1" name="Bob Black">
<ContactInfo email="bb@companyA.com" phone="(123) 456-7890" />
<Login uid="bb" pwd="bb7" />
</Contact>
<Contact id="2" name="Bob Brown">
<ContactInfo email="bbn@companyA.com" phone="(123) 456-7891" />
<Login uid="bn" pwd="bn1" />
</Contact>
<Contact id="3" name="Bob White">
<ContactInfo email="bw@companyA.com" phone="(123) 456-7892" />
<Login uid="bw" pwd="bw2" />
</Contact>
</Contacts>
</Company>
</Companies>
Because the for xml path ('Contact'), root('Contacts'), type clause for the Contacts sub query included a root('Contacts'), the Contact nodes are placed inside a Contacts node under Company.
Now that we have a Stored Procedure that produces XML that contains Company information along with all the associated Contacts in one query, all we have to do is deserialize that XML output to a CompanyList object. And, using the SqlXmlData class included in the demo project, there is very little code you have to write to use this approach. All you have to do beyond that is ensure that your model that you are deserializing to has the XMLSerializer attributes necessary to mirror the XML produced.
Recap
XML Serializable Models
As mentioned above, I have an article on the subject of Using the XmlSerializer Attributes. If you have sample XML or an XSD schema to work from, you can use tools like XSD.exe or Skeleton Crew to generate XML Serializable code models. And, if you don't already have a model, you might want to start with the XML structure and generate code from there. If you already have a model that's not XML serializable, the article above can help you pepper your model with the attributes necessary to produce the XML you want.
XML Queries
Documentation on the Path mode of the FOR XML clause seems to be spread fairly thin. Most write-ups I could find on the subject were basic. But with some experimentation, hopefully, you can make it do what you need. In any event, these are a couple of the references I found:
- Moving FOR XML EXPLICIT to the new PATH mode (part II)
- What's New in FOR XML in Microsoft SQL Server 2005
XMLSerializer
You can, of course, re-use the demo project's SqlXmlData class. You can change it to suit your needs. Or, you can start from scratch and create your own. I chose to specifically use the System.Data.SqlClient implementations because, to my knowledge, SQL Server 2005 is the only engine that supports the FOR XML clause, but I could very well be wrong about that.
Conclusion
Of course, this doesn't help when it comes to inserting or updating information. But, selecting complex structured data is usually much more tedious, time consuming, and error prone than inserts and updates. I'm sure there are situations where even this method would be complicated, and maybe not even feasible.
How you approach this depends on your scenario. If you've already got a model that's XML serializable, you'll want to create queries that mirror that XML structure. If your model isn't yet XML serializable, then you have a clean slate to start with, and can decide from your model and your data how you want the two to meet in XML.
After my disappointment in the complexity of the Explicit mode of FOR XML with SQL Server 2000, I was extremely excited to see the Path mode. I hope this helps some people see the great possibilities that SQL Server 2005 provides through this method. The Path mode, coupled with XML serialization, makes for a powerful combination of tools for selecting complex data.