Saving InfoPath Form to SQL Server

Microsoft SQL Server 2005 introduced a new data type, the XML data type. Now you can associate an XML schema with XML table columns to force them to adhere to constraints defined within the specified schema. Such XML table columns are ideal to save InfoPath forms in, since InfoPath forms are based on XML schemas and have XML data as output.

In this article we will set up a database table that has an XML column associated with the XML schema of an InfoPath form. We will then use an ASP.NET page to submit and save InfoPath forms to the database table. We will also discuss common challenges you may encounter when trying to set up such a solution.

This article assumes that you have experience developing InfoPath forms, working with SQL Server and Internet Information Server, and that you are familiar with C# and ASP.NET.

Software requirements

• Microsoft Office InfoPath 2003 SP2

• Microsoft SQL Server 2005

• Microsoft Visual Studio .NET 2005

• Microsoft Visual C#

• Internet Information Server (IIS)

Design the InfoPath form


Create a new InfoPath form that has a Date Picker control, a Text Box control, a Rich Text Box control, a File Attachment control, and a Picture control
To be able to save this form to SQL Server as typed XML, i.e. XML data that has been associated with an XML schema, you must know what the XML schema for the InfoPath form is before you can create the database table. Perform the following steps to extract the XML schema for the InfoPath form:

1. On the File menu, click Extract Form Files.

2. In the Browse for Folder dialog box, select a folder to save the extracted files in and click OK.

3. Open Windows Explorer and navigate to the folder where you saved the extracted files.

4. Open the myschema.xsd file to view and copy the XML schema for the InfoPath form (see Listing 1).

XML schema for the InfoPath form

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<xsd:schema

targetNamespace="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-12-16T23:41:41"

xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-12-16T23:41:41"

xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="myFields">

<xsd:complexType>

<xsd:sequence>

<xsd:element ref="my:field1" minOccurs="0"/>

<xsd:element ref="my:field2" minOccurs="0"/>

<xsd:element ref="my:field3" minOccurs="0"/>

<xsd:element ref="my:field4" minOccurs="0"/>

<xsd:element ref="my:field5" minOccurs="0"/>

</xsd:sequence>

<xsd:anyAttribute processContents="lax" namespace="http://www.w3.org/XML/1998/namespace"/>

</xsd:complexType>

</xsd:element>

<xsd:element name="field1" nillable="true" type="xsd:date"/>

<xsd:element name="field2" type="xsd:string"/>

<xsd:element name="field3">

<xsd:complexType mixed="true">

<xsd:sequence>

<xsd:any minOccurs="0" maxOccurs="unbounded"

namespace="http://www.w3.org/1999/xhtml" processContents="lax"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="field4" nillable="true" type="xsd:base64Binary"/>

<xsd:element name="field5" nillable="true" type="xsd:base64Binary"/>

</xsd:schema>

XML schema collections and InfoPath

Typed XML table columns in SQL Server 2005 are columns that have been associated with an XML schema and untyped XML table columns are those that have no XML schema associated with them. An XML schema provides the following:

• Validation constraints

• Data type information about the instance data

An untyped XML table column is best suited to save XML data for XML schemas that are not known in advance or in cases where an XML schema is rejected by SQL Server (see Limitations of XML schema collections later on in this article).

You can use XML schema collections in SQL Server to define XML schemas. XML schema collections must be created first before you can associate them with XML table columns. You can use the following T-SQL syntax to create an XML schema collection:

CREATE XML SCHEMA COLLECTION [<relational_schema>.]sql_identifier AS Expression

For example,

CREATE XML SCHEMA COLLECTION IPFormSchemaCollection AS

'<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<xsd:schema targetNamespace="<span class=Italic>namespace_of_form</span>">

...

</xsd:schema>'

creates an XML schema collection for an InfoPath form.

For more information on creating XML schema collections see CREATE XML SCHEMA COLLECTION (Transact-SQL) in SQL Server 2005 Books Online.

Saving forms with different XML schemas to one table column

You can define more than one XML schema in one XML schema collection. Therefore, you can also save multiple forms with different XML schemas in one XML table column by creating an XML schema collection that consists of the XML schemas for multiple InfoPath forms and associating this XML schema collection with the XML table column to save the forms in.

The following sample T-SQL statement defines the XML schemas for two different InfoPath forms in one XML schema collection:

CREATE XML SCHEMA COLLECTION IPFormSchemaCollection AS

'<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<xsd:schema targetNamespace="<span class=Italic>namespace_first_form</span>">

...

</xsd:schema>

<xsd:schema targetNamespace="<span class=Italic>namespace_second_form</span>">

...

</xsd:schema>'

Limitations of XML schema collections

XML schema collections have a few known limitations which you can read more about in Guidelines and Limitations in Using XML Schema Collections on the Server. This means that you cannot just copy and paste the XML schema of your InfoPath form to create an XML schema collection for it, but that you will have to modify it in such a way that SQL Server will accept it. Please note that these modifications are only required for the XML schema you will be using to create the XML schema collection in SQL Server and not for the XML schema of your InfoPath form itself. The latter may remain intact. Any modifications you apply to the schema should ensure that the XML data of filled out InfoPath forms conforms to both the XML schema collection in SQL Server as well as the original XML schema that was defined for your InfoPath form. In simple terms: One XML document should "fit" two XML schemas. Alternatively, you could create an XML schema that is accepted by SQL Server and then base your InfoPath form on this schema, which would result in only one schema being used for both SQL Server and InfoPath.

The form in Figure 1 has two controls that are likely to cause XML validation errors in SQL Server:

1. The Date Picker control

2. The Rich Text Box control

In the next 3 sections we will take a closer look at how you can modify the XML schema in Listing 1 so that it passes validation within SQL Server 2005, while XML data that is derived from this schema still adheres to the original XML schema of the InfoPath form.

The processContents attribute

The first error message that might appear when you try to create an XML schema collection using the XML schema as shown in Listing 1 is:

The XML Schema syntax 'processContents="lax"' is not supported.

Since SQL Server does not support lax validation for elements of the anyType type, strict or skip validation should be used instead of lax. The Rich Text Box control in InfoPath uses lax validation, because the elements contained within a field representing a Rich Text Box control cannot be defined in advance. Therefore, if you are using a Rich Text Box control on your form, set its processContents attribute in the XML schema to skip. Modify the XML schema in Listing 1 so that

<xsd:anyAttribute processContents="lax" namespace="http://www.w3.org/XML/1998/namespace"/>

becomes

<xsd:anyAttribute processContents="strict" namespace="http://www.w3.org/XML/1998/namespace"/>

and

<xsd:any minOccurs="0" maxOccurs="unbounded" namespace="http://www.w3.org/1999/xhtml" processContents="lax"/>

becomes

<xsd:any minOccurs="0" maxOccurs="unbounded" namespace="http://www.w3.org/1999/xhtml" processContents="skip"/>

The XML document encoding

XML documents can be encoded using encodings such as UTF-8, UTF-16, etc. SQL Server 2005 stores data in Unicode (UTF-16) by default. If XML encoding information is specified using the encoding attribute in the XML declaration, this encoding must be compatible with the string's code page. For example, XML data that has a UTF-16 encoding should be passed in as an nvarchar string, while XML data that has a UTF-8 encoding should be passed in as a varchar string. InfoPath forms use UTF-8 encoding, so you must use a varchar string when creating the XML schema collection for your InfoPath form.

Prefixing a string with an N defines it as a Unicode string. For example, N'Double-byte' defines the text "Double-byte" as an nvarchar string, while 'Single-byte' defines the text "Single-byte" as a varchar string. If you use a Unicode string when creating the XML schema collection for your InfoPath form, you will receive the following error:

XML parsing: line ..., character ..., unable to switch the encoding.

This is because UTF-16 encoded strings are inconsistent with the encoding defined in the XML schema for InfoPath forms (UTF-8). You can get around this by not using the N prefix when creating the XML schema collection.

Date and dateTime elements

While the XML parser might not complain when you create an XML schema collection that contains an XML element with a date or dateTime type, it will complain when you try to save date/time strings produced by InfoPath into such elements. InfoPath's date/time strings have the format YYYY-MM-DD or YYYY-MM-DDThh:nn:ss and do not contain time zone information, so are rejected by SQL Server.

You can get around this by changing the data type for the XML element that represents a Date Picker control to string and use a regular expression to add a restriction on the format of text that may be entered into this field. So you must modify the XML schema for the Date Picker from

<xsd:element name="field1" nillable="true" type="xsd:date"/>

into

<xsd:element name="field1">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="10" />

<xsd:pattern value="\d\d\d\d-\d\d-\d\d" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

There are several more limitations to the XML schema collection database object which have not been discussed here and are beyond the scope of this article, but you are encouraged to read the Guidelines and Limitations in Using XML Schema Collections on the Server to find workarounds whenever possible or advised to use untyped XML table columns when workarounds cannot be found.

Creating the Database Objects

Creating the XML schema collection

Once you have modified the XML schema for the InfoPath form, perform the following steps to create the XML schema collection:

1. Open SQL Server Management Studio.

2. Select the database you want to create the table to save your InfoPath forms in.

3. Open a new Query window.

4. Run the T-SQL statement shown in Listing 2.

Note: You will have to give users EXECUTE permission on the XML schema collection before it can be used.

CREATE XML SCHEMA COLLECTION IPFormSchemaCollection AS

'<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<xsd:schema targetNamespace="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-12-16T23:41:41"

xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-12-16T23:41:41"

xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<xsd:element name="myFields">

<xsd:complexType>

<xsd:sequence>

<xsd:element ref="my:field1" minOccurs="0"/>

<xsd:element ref="my:field2" minOccurs="0"/>

<xsd:element ref="my:field3" minOccurs="0"/>

<xsd:element ref="my:field4" minOccurs="0"/>

<xsd:element ref="my:field5" minOccurs="0"/>

</xsd:sequence>

<xsd:anyAttribute processContents="strict" namespace="http://www.w3.org/XML/1998/namespace"/>

</xsd:complexType>

</xsd:element>

<xsd:element name="field1">

<xsd:simpleType>

<xsd:restriction base="xsd:string">

<xsd:maxLength value="10" />

<xsd:pattern value="\d\d\d\d-\d\d-\d\d" />

</xsd:restriction>

</xsd:simpleType>

</xsd:element>

<xsd:element name="field2" type="xsd:string"/>

<xsd:element name="field3">

<xsd:complexType mixed="true">

<xsd:sequence>

<xsd:any minOccurs="0" maxOccurs="unbounded"

namespace="http://www.w3.org/1999/xhtml" processContents="skip"/>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

<xsd:element name="field4" nillable="true" type="xsd:base64Binary"/>

<xsd:element name="field5" nillable="true" type="xsd:base64Binary"/>

</xsd:schema>'

GRANT EXECUTE ON XML SCHEMA COLLECTION::IPFormSchemaCollection TO PUBLIC


Creating the database table

Once you have created the XML schema collection, perform the following steps to create the database table:

1. Open SQL Server Management Studio.

2. Select the database you want to create the table to save InfoPath forms in.

3. Open a new Query window.

4. Run the T-SQL statement.


CREATE TABLE IPForms

(

Id INT PRIMARY KEY IDENTITY NOT NULL,

Form XML(DOCUMENT IPFormSchemaCollection) NOT NULL

)


This table uses the XML schema collection that was created in the previous section to strongly type the Form column that has the XML data type defined on it.

Saving the InfoPath Form to the Database

Creating an ASP.NET page to submit the form to the database

You could use either a web service or a web page to submit the InfoPath form to the database. Here, you will use an ASP.NET web page to submit and save the XML data for the form to SQL Server.

Create a new web application in Microsoft Visual Studio 2005 and add the C# code shown in Listing 4 to the Page_Load event handler of the Default.aspx page of the web application. Do not forget to replace the values for the ServerName and DBName with the correct values for your own situation.

Code in the Page_Load event handler of the ASP.NET page

System.Xml.XPath.XPathDocument xDoc =

new System.Xml.XPath.XPathDocument(Request.InputStream);

System.Xml.XPath.XPathNavigator navigator = xDoc.CreateNavigator();



using (System.Data.SqlClient.SqlConnection conn =

new System.Data.SqlClient.SqlConnection(

"Data Source=ServerName;Initial Catalog=DBName;Integrated Security=True"))

{

conn.Open();



if (!String.IsNullOrEmpty(navigator.InnerXml))

{

System.Data.SqlClient.SqlCommand cmd =

new System.Data.SqlClient.SqlCommand(

@"INSERT INTO IPForms VALUES (@Form)", conn);

cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter(

"@Form", navigator.InnerXml));

cmd.ExecuteNonQuery();

}



conn.Close();

}

A brief explanation of the code in Listing 4 follows.

System.Xml.XPath.XPathDocument xDoc =

new System.Xml.XPath.XPathDocument(Request.InputStream);

This retrieves the binary stream that represents the XML contents of the InfoPath form from the Request object and uses it to instantiate an XPathDocument object.

System.Xml.XPath.XPathNavigator navigator = xDoc.CreateNavigator();

This creates an XPathNavigator object to be able to navigate through and retrieve nodes within the XML of the XPathDocument object. The rest of the code opens a database connection and uses

navigator.InnerXml

which returns the XML for the entire InfoPath document that was submitted to the web server to write the XML data of the InfoPath form to the database.

Note: You will have to give users permissions to access your database. In a standard configuration, web sites run under the Network Service account on Windows Server 2003 operating systems, so this account will require access to the database as well as INSERT permission on the table.

Configuring the InfoPath form to submit its data to a Web Server

Perform the following steps to configure the InfoPath form to submit its XML data to the ASP.NET page created in the previous section:

1. On the Tools menu, click Submitting Forms.

2. In the Submitting Forms dialog box, select Enable Submit command and buttons.

3. In the Submit to drop-down list box, select Web server (HTTP).

4. In the URL text box, type in the URL to the ASP.NET page you created in the previous section.

5. Click OK.

You should now be able to fill out your InfoPath form and submit its XML data to the web page, which in turn will save the data to a strongly typed XML table column within SQL Server.



Conclusion

In this article you saw how to take advantage of the new XML data type and XML schema collection database object within SQL Server 2005 to save InfoPath forms as strongly typed XML to a database table. With XML schema collections you can define multiple XML schemas on one XML table column and, thus, also save InfoPath forms with different XML schemas to one XML table column.

Due to limitations of XML schema collections in SQL Server, you will not always be able to use typed XML table columns to save your InfoPath forms in, so you may have to resort to using untyped XML table columns.

Finally, you saw how to use an ASP.NET page to submit your InfoPath forms to SQL Server and save them as XML.