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.
Listing 2
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>'
GO
GRANT EXECUTE ON XML SCHEMA COLLECTION::IPFormSchemaCollection TO PUBLIC
GO
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 shown in Listing 3.
Listing 3
CREATE TABLE IPForms
(
Id INT PRIMARY KEY IDENTITY NOT NULL,
Form XML(DOCUMENT IPFormSchemaCollection) NOT NULL
)
GO
GRANT INSERT ON IPForms TO PUBLIC
GO
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.
