Saving InfoPath Forms to SQL Server 2005 as XML
page 5 of 7
by S.Y.M. Wong-A-Ton
Feedback
Average Rating: 
Views (Total / Last 10 Days): 51635/ 130

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.

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.


View Entire Article

User Comments

No comments posted yet.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2020 ASPAlliance.com  |  Page Processed at 2020-08-03 10:57:44 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search