InfoPath and Yukon: The Details
In a recent post, I touched upon the reasons why you might want to go with SQL Server XML columns as the storage for your InfoPath forms. In this article, we'll talk about actually making it work.
InfoPath's strength is working with XML data sources: web services. In the implementation below, we'll write a simple web service that will help us retrieve and submit the data to Yukon.
Scenario
Build a resume database for the HR department. We want to store highly structured resumes in a database. Highly structured here is the opposite of freeform: a resume in our scenario isn't a blob of text; we have the graduation date, employment dates, actual titles and other things parsed into data structures.
Data Structure
We will store candidate records as XML blobs in a Yukon XML column. Each resume will be stored as a separate record; each record will also have a primary key - a JobCandidateID - to simplify our development. We'll define only one table, called JobCandidate:
Populate the table with a few Resume XML files that all satisfy the same schema. Store the schema somewhere where the web server can access it.
Core Implementation in English: we'll write a web service as a middle tier between SQL Server 2005 and the InfoPath form. This web service will have two methods:
1) GetCandidateRecord: given a JobCandidateID (an integer), return a DataSet (for our purposes, an XSD-typed XML blob) that contains the candidate record. Give me the job candidate ID, I'll give you his resume.
2) UpdateCandidateRecord: take a JobCandidateID (an integer) and a Resume (an XML document), and update the resume of the candidate with that particular ID to the resume passed in as a second parameter. Nothing fancy, really.
Core Implementation in C#
I promised you two methods, here they are. First, GetCandidateRecord.
[WebMethod]
public DataSet GetCandidateRecord(int JobCandidateID)
{
DataSet result = null;using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandText = @"
SELECT Resume
FROM " + tableName + @"
WHERE JobCandidateID = @x";
command.Parameters.Add("@x", SqlDbType.Int);
command.Parameters[0].Value = JobCandidateID;
SqlDataReader reader = command.ExecuteReader();if (reader.Read())
{
DataSet ds = new DataSet();
ds.ReadXmlSchema(@"C:\Inetpub\wwwroot\infopath_yukon\Resume.xsd");
XmlDataDocument xd = new XmlDataDocument(ds);
xd.Load(new StringReader((string)reader.GetValue(0)));
result = xd.DataSet;
}
conn.Close();
return result;
}
}
Things are fairly straightforward here:
- Open a SqlConnection using ASP.NET credentials (make sure the ASPNET user has read/write rights to the database).
- Build a simple SELECT statement to return a resume. Recall that the resume is just an XML document stored as-is in the database.
- Cast the resume dataset into a typed dataset by applying a schema stored somewhere on the web server. Oh, I forgot to tell you - you need a schema :-). Why? InfoPath form needs to know what to expect from the web service, and while InfoPath can infer the shape of the data from the instance, this method is very much error prone. For example, how can InfoPath know of a repeating structure if only one instance was present in a sample XML document? How about choice or optional structures? Because of all of these reasons, you need to provide a typed dataset through your web service.
- Return the typed dataset for the Resume record.
Next, let's look at UpdateCandidateRecord.
[WebMethod]
public void UpdateCandidateRecord(XmlDocument xml, int JobCandidateID)
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlCommand command = conn.CreateCommand();command.CommandText = @"
UPDATE " + tableName + @"
SET Resume = @x
WHERE JobCandidateID = @y";
command.Parameters.Add("@x", SqlDbType.Xml);
command.Parameters[0].Value = xml.InnerXml.ToString();
command.Parameters.Add("@y", SqlDbType.Int);
command.Parameters[1].Value = JobCandidateID;
command.ExecuteNonQuery();
conn.Close();
}
}
- Open a SqlConnection
- Build a simple UPDATE statement to save the resume for a given candidate. Note that you must use SqlCommand Parameters: just concatenating the XML blob won't do.
- Execute the UPDATE statement. Note that we are replacing the entire resume with the new one; no partial updates are done. This means that simultaneous editing of Resume records won't be possible.
Basic Form Template
Now that the web service is set up, we can easily build a form template based on it. The template may or may not be browser-enabled; the method described here works with both. Just launch InfoPath designer, and pick "start from web service" as your data source. Specify GetCandidateRecord as the "receive" piece of the web service, and UpdateCandidateRecord as the submit part.
InfoPath will ask you for sample JobCandidateID values for the receive web service; since our database already has a few Resumes, we can type in the JobCandidateID for one of them. You may be wondering - wait, I thought InfoPath won't do the schema inference by example! It won't - the dataset returned by your web service will contain a schema (that's why we called DataSet.ReadXmlSchema() in GetCandidateRecord), and InfoPath will use that schema to build your data source tree.
After you've gone through the initial data connection setup, you'll notice that your main data source tree is correctly populated with the data types from your schema. Complex structures should show up just fine - repeating, optional, choice structures, non-string datatypes, XSD validation... And the Submit button should be configured to save the modified Resumes back to SQL Server.
FAQ
1. Why do we have to use a custom web service, and not built-in Yukon web services ?
There are unfortunate technical limitations that currently require you to write a custom web service to work with SQL Server 2005 in a manner described above. The web service is, as you saw, very easy; we know that this is something that can be made better, and will consider addressing this in future versions of InfoPath and SQL Server.
2. Why not XSD-typed XML columns?
When InfoPath submits datasets to the web service, it adds dataset tracking information; while you can add optional attributes to your InfoPath-generated schema and upload it to Yukon, this would complicate maintenance quite a bit.
3. What other resources are available on the topic?
Be sure to check out this article by S.Y.M. Wong-A-Ton.
Alex Weinstein
Program Manager
Comments
Anonymous
February 12, 2007
When I try to deploy this Web service on Vista, I get the following exception: -- cut here -- Exception Details: System.Runtime.Serialization.InvalidDataContractException: Type 'System.Xml.XmlDocument' cannot be serialized. Consider marking it with the DataContractAttribute attribute, and marking all of its members you want serialized with the DataMemberAttribute attribute. -- cut here -- Can you provide updated code ? -ArunAnonymous
February 12, 2007
Adding [XmlSerializerFormat] got away with that error.Anonymous
February 12, 2007
When connecting to SQLServer, I'm getting the following error: -- cut here -- An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) <detail><ExceptionDetail xmlns="http://schemas.datacontract.org/2004/07/System.ServiceModel" xmlns:i="http://www.w3.org/2001/XMLSchema-instance"><HelpLink i:nil="true"/><InnerException i:nil="true"/><Message>An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)</Message><StackTrace> at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) -- cut here -- This is in spite of the fact that I've enabled local and remote connection ni SQL Server Express following http://support.microsoft.com/default.aspx/kb/914277 and also restarted IIS and SQL Server. Any tips ? -ArunAnonymous
February 13, 2007
Arun, I'm glad you're trying this out! I'm afraid I can't help with the last issue - this looks like a generic configuration issue with SQL Server; maybe try the SQL discussion board?Anonymous
February 13, 2007
I enabled local connection on SQL server and got past that problem. Do you have a download bundle that can be used to install the demo ?Anonymous
February 15, 2007
The comment has been removedAnonymous
February 16, 2007
SQL Server 2005, also known as Yukon, comes with greatly improved XML support . Just look at it: nativeAnonymous
February 19, 2007
The comment has been removedAnonymous
March 04, 2009
I am entirely new to SQL, Web Services, and accessing typed XML data in SQL. This tutorial is very helpful, although slightly confusing. I was able to get most of the code working by replacing connString with my database specifications (server, uid, pwd, database) and importing additional classes to support XmlDocument and SQLConnection code. But am now having problems with the FROM " + tableName + @" portion of the code. VS says that 'tableName' does not exist in the current context. Does this value need replaced by the actual table name, for instance "HumanResources.JobCandidate" in this example? Also are there any other references on this type of information other than the other paper referenced? I want to use stored XML schema and ASP.NET web services as this described for InfoPath (NOT native SQL web services or stored relational tables). Is there any sort of demonstration available for this yet?