Microsoft InfoPath 2010
The official blog of the Microsoft InfoPath team

InfoPath and Yukon: The Details

InfoPath and Yukon: The Details

Rate This
  • Comments 9

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

Leave a Comment
  • Please add 4 and 6 and type the answer here:
  • Post
  • 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 ?

    -Arun

  • Adding [XmlSerializerFormat] got away with that error.

  • 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 ?

    -Arun

  • 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?

  • 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 ?

  • I'm afraid we don't currently have a shareable demo. If you have specific questions as to how to set a component of this solution up, please let us know.

    -Alex

  • SQL Server 2005, also known as Yukon, comes with greatly improved XML support . Just look at it: native

  • Alex-

    Thanks for getting this together!

    I think a very relevant follow-up would be "Managing Schema Changes in Infopath Forms". What happens when the form you use to create XML data needs to change... adding or removing fields, changing logic, etc.

    It's cool to be able to save XML data to SQL2005, but eventually the form you use to display this data is going to want to change. The schema will be different, you might have removed or added nodes, etc. What happens to your data stored in the XML field? Can you still bring it up? Will IP choke because the XML doesn't match the form? Once it goes into SQL, do you have to always bring it up with the original form version?

    I've done a wee bit of testing around this, and I've figured out a few things:

    1) If you *add* an optional field to the schema, you're fine. If Infopath has an optional node it it's schema, but that node isn't present in the XML, it doesn't complain, it just ignores it. Makes sense.

    2) If you *remove* a node from the schema, Infopath will not load the old XML... it will throw an error saying it's get an unexpected node in the XML... not defined by the schema.  

    2a) To get around 2) you could just follow a policy to never delete nodes... just leave them in the schema even if they are no longer used in the form. That makes me feel dirty.. and after a few iterations it will make your schema look dirty and confusing as well... and it doesn't cover changing a field from optional to required, etc.

    You could train to become an xquery ninja and update all your source XML in SQL to fit whatever the latest schema is, but that seems a lot like juggling swords to me.

    Usually, it seems Infopath just assumes it will always use whatever form/schema was used to generate the XML when it opens it back up again. Realistically, this can't always be the case, especially when reloading data out of a SQL server as above. If you want to change your form's functionality, get rid of something thats no longer used (or has been moved to a different process/data store), etc... what to do?

    Some insight from the IP gods would be interesting. =)

  • 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?

Page 1 of 1 (9 items)