Click here to Skip to main content
Click here to Skip to main content

Dynamically Create, Populate and Submit an InfoPath Form to a SharePoint Form Library

, 12 Sep 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
This article illustrates a technique used to dynamically create, populate and submit an InfoPath form to a SharePoint form library using data from a SQL database.

Introduction

SharePoint form library is a great tool for hosting and serving browser enabled InfoPath forms. Sometimes, we may want to pre-populate a form with personalized data from a database and then let user add additional information or make corrections on the form. This article illustrates a technique used to dynamically create, populate and submit an InfoPath form to a SharePoint form library using data from a SQL database.

InfoPath Form Template

An InfoPath form template is authored and published to a SharePoint form library using Microsoft InfoPath Designer. If you are not familiar with InfoPath designer and SharePoint form library, please read about it before diving into the article.

A sample form template has been designed for collecting the employee’s basic contact, education and job history data, as shown in the screen shot below.

Contact information including name, phone number, fax number, email, etc. is entered using Text Box and Date Picker controls, while education and job history are collected using a Repeating Section control as shown with light gray background on the form. Upon clicking on a repeat link, all fields (data controls) contained in a Repeating Section are duplicated to allow multiple data entries.

Template.xml File

An InfoPath form template is saved as an .xsn file. It is in fact a collection of multiple files. Among them, template.xml file is what we are most interested in. This is an XML document that contains all data elements (controls) placed on the form template.

Listing 1 shows the complete source code of the template.xml document.

Listing 1: 

<?xml version="1.0" encoding="UTF-8"?>
<?mso-infoPathSolution 
   name="urn:schemas-microsoft-com:office:infopath:EmployeeInfoPathSources:-myXSD-2013-01-10T17-12-49" 
   href="manifest.xsf" solutionVersion="1.0.0.30" productVersion="14.0.0" PIVersion="1.0.0.0" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.3"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
    xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2013-01-10T17:12:49"  
    xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
	<my:FirstName></my:FirstName>
	<my:LastName></my:LastName>
	<my:EducationTable>
		<my:EducationRow>
			<my:SchoolName></my:SchoolName>
			<my:Degree></my:Degree>
			<my:GraduationDate></my:GraduationDate>
		</my:EducationRow>
	</my:EducationTable>
	<my:PhoneNumber></my:PhoneNumber>
	<my:Fax></my:Fax>
	<my:Email></my:Email>
	<my:HireDate xsi:nil="true"></my:HireDate>
	<my:JobHistoryTable>
		<my:JobHistoryRow>
			<my:Company></my:Company>
			<my:Position></my:Position>
			<my:Level></my:Level>
			<my:Supervisor></my:Supervisor>
			<my:TerminationDate xsi:nil="true"></my:TerminationDate>
		</my:JobHistoryRow>
	</my:JobHistoryTable>
</my:myFields> 

The XML document starts with a standard XML declaration <?xml version="1.0" encoding="UTF-8"?>, followed by two processing instructions: <?mso-infoPathSolution…?> and <?mso-application…?>. These instructions help Microsoft software recognize an InfoPath document. Data elements start with the root <my:myFields… >, which requires three attributes for InfoPath namespaces. Each data element inside the root consists of “my:” plus an actual field name on the form template.

In the SharePoint form library, when the form template is opened to enter data, a new form is created and saved into the library. The image below shows a sample form filled with data and saved in the library.

Behind the scene, this too is an XML document. Listing 2 shows complete source code of the document.

Listing 2:

<?xml version="1.0" encoding="utf-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:EmployeeInfo:-myXSD-2013-01-10T17-12-49" href="http://MySharePointServer/EmployeeInfo/forms/template.xsn" solutionVersion="1.0.0.30" productVersion="14.0.0" PIVersion="1.0.0.0"?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.3"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2013-01-10T17:12:49">
  <my:ID>1</my:ID>
  <my:FirstName>Magnolia</my:FirstName>
  <my:LastName>Flower</my:LastName>
  <my:EducationTable>
    <my:EducationRow>
      <my:SchoolName>Eastern Ohio University</my:SchoolName>
      <my:Degree>Bachelor</my:Degree>
      <my:GraduationDate>1994-05-30</my:GraduationDate>
    </my:EducationRow>
    <my:EducationRow>
      <my:SchoolName>Twin Peaks High</my:SchoolName>
      <my:Degree>HS Diploma</my:Degree>
      <my:GraduationDate>1990-06-12</my:GraduationDate>
    </my:EducationRow>
  </my:EducationTable>
  <my:PhoneNumber>123-456-7890</my:PhoneNumber>
  <my:Fax />
  <my:Email>jsmith@DynamicSQL.com</my:Email>
  <my:HireDate>2009-02-25</my:HireDate>
  <my:JobHistoryTable>
    <my:JobHistoryRow>
      <my:Company>BCD Ltd.</my:Company>
      <my:Position>Director</my:Position>
      <my:Level>Senior</my:Level>
      <my:Supervisor>Ms. Applegate</my:Supervisor>
      <my:TerminationDate>2008-07-01</my:TerminationDate>
    </my:JobHistoryRow>
    <my:JobHistoryRow>
      <my:Company>CDE Inc</my:Company>
      <my:Position>Intermediate Engineer</my:Position>
      <my:Level>Mid-Level</my:Level>
      <my:Supervisor>John Smith</my:Supervisor>
      <my:TerminationDate>2002-05-03</my:TerminationDate>
    </my:JobHistoryRow>
    <my:JobHistoryRow>
      <my:Company>ABC Inc.</my:Company>
      <my:Position>Engineer</my:Position>
      <my:Level>Junior</my:Level>
      <my:Supervisor>Mr. Hicks</my:Supervisor>
      <my:TerminationDate>1994-01-02</my:TerminationDate>
    </my:JobHistoryRow>
  </my:JobHistoryTable>
</my:myFields> 

The XML document in Listing 2 is very similar to the template.xml in Listing 1. However, this document is populated with data, while template.xml only has empty XML data elements. Another difference is noticeable in the processing instruction “mso-infoPathSolution”, in which the URL value for href in template.xml is “manifest.xsf” but in Listing 2 is http://MySharePointServer/EmployeeInfo/forms/template.xsn, a full URL pointing to the template.xsn file on a SharePoint server. The URL consist of following segments: [SharePoint Server URL]/[Form Library Name]/forms/template.xsn. In our example, the fictional SharePoint server URL is http://MySharePointServer/, and the Form Library Name is EmployeeInfo.

You may also notice that <my:EducationRow> as well as its children data elements are repeated. This is simply because two education records are entered. Similarly, <my:JobHistoryRow> occurs three times because of three job history records.

Comparing XML elements in Listing 2 with template.xml, a conclusion may be drawn. To programmatically create an InfoPath form, populate it with data from a SQL database, and then submit it to a SharePoint form library, what needs to be done is to create an XML document similar to Listing 2 using employee’s data from the database, and then upload it to the library.

Naming Convention

In order to create and populate an InfoPath form with personalized data from a SQL database, a proper naming convention for data fields is required. In the design view of InfoPath Designer 2010, the form template looks like the image below.


The left side shows various InfoPath data controls and the right side displays a field list generated by the designer as controls are placed on to the template. Each control on the form corresponds to a data field in the list. Let’s take a closer look at the field list.


When a data control is placed on to the template, a default name is given in the field list. The above image illustrates the default names shown on the right and the actual names on the left after being renamed. When a Repeating Section control is placed on to the template, the designer generates two default names: group1 and group2. Group1 is the outer envelope and group2 contains actual data fields such as a Text Box or a Date Picker, etc. Similarly, when the second Repeating Section control is placed, the field names default to group3 and group4. Here, the first Repeating Section is intended to collect employee’s education information and the second for job history information.

It is important to note that group1 and group3 (the outer envelopes) are renamed to the names ending with a key word “Table”. That is EducationTable and JobHistoryTable respectively. Group2 and group4 are renamed to that ending with a key word “Row”, EducationRow and JobHistoryRow. When entering data, the EducationRow and JobHistoryRow as well as the data fields they include are the actual repeating items. This naming convention plays an important role in creating and populating an InfoPath form dynamically. For the rest of the fields, it may not be necessary to follow a specific naming convention. However, starting with the same phrase for all field names within a Repeating Section, such as “Education” or “Job” certainly makes them easy to read and avoids confusion.

Another important aspect in the naming convention is that each field name in the field list matches a column name returned from the SQL database. In other words, the returned column names are exactly the same as in the field list so that an XML document can be dynamically generated using column names as XML element names and column values as XML element values.

Database Work

Three relational tables, Employee, Education and JobHistory are used to maintain employee information as illustrated in the diagram below. The relationship indicates that an employee may have multiple entries in Education and JobHistory tables. The FormLibrary_Info table stores InfoPath form and library attributes, such as InfoPath namespace, processing instruction, form library URL, form template URL etc. These are required when an InfoPath form XML document is created and uploaded.



Basic contact information on a form, such as name, phone number, email, etc., corresponds to a single record in the database, known as the Top Level record, while education and job history in Repeating Sections are associated with multiple records, known as the Child Level records.

Four stored procedures are involved: sp_InfoPathDB_FormLibrary_Info_select, sp_InfoPathDB_Employee_Select_TopLevel, sp_InfoPathDB_Education_Select_ChildLevel and sp_InfoPathDB_JobHistory_Select_ChildLevel as shown in Listing 3, 4, 5 and 6. 

Listing 3:

CREATE PROCEDURE [dbo].[sp_InfoPathDB_FormLibrary_Info_select] 
    @ID int
AS
BEGIN
    SELECT * FROM dbo.FormLibrary_Info
    WHERE ID=@ID
END
 Listing 4:
CREATE PROCEDURE [dbo].[sp_InfoPathDB_Employee_Select_TopLevel]
    @ID int
AS
BEGIN
    -- sp_InfoPathDB_Employee_Select_TopLevel @ID=2
SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,'' AS EducationTable
      ,[PhoneNumber]
      ,[Fax]
      ,[Email]
      ,LEFT(ISNULL(CONVERT(VARCHAR(20), [HireDate], 120), ''), 10) AS HireDate
        ,'' AS JobHistoryTable       
      FROM [Employee]
      WHERE ID=@ID
END
 Listing 5:
CREATE PROCEDURE [dbo].[sp_InfoPathDB_Education_Select_ChildLevel]
    @ID int
AS
BEGIN
    -- sp_InfoPathDB_Education_Select_ChildLevel @ID=2
    SELECT [SchoolName]
          ,[Degree]
          ,LEFT(ISNULL(CONVERT(VARCHAR(20), [GraduationDate], 120), ''), 10) AS GraduationDate
    FROM [Education]
    WHERE ID=@ID
    ORDER BY GraduationDate DESC 
END
 Listing 6:
CREATE PROCEDURE [dbo].[sp_InfoPathDB_JobHistory_Select_ChildLevel] 
    @ID int
AS
BEGIN
    -- sp_InfoPathDB_JobHistory_Select_ChildLevel @ID=2
    SELECT [Company]
             ,[Position]
             ,[Level]
             ,[Supervisor]
        ,LEFT(ISNULL(CONVERT(VARCHAR(20), [TerminationDate], 120), ''), 10) 
                  AS [TerminationDate]
    FROM [JobHistory]
    WHERE ID=@ID
      ORDER BY TerminationDate Desc
END

Listing 3 retrieves InfoPath form and library attributes for XML document header.

Listing 4 pulls a single Top Level record for basic contact information, and Listing 5 and 6 pull multiple Child Level records for education and job history. It can be seen that all column names returned from the stored procedures are exactly the same as that in the field list of the form template.

The Top Level record from Listing 4 includes two special columns, named as EducationTable and JobHistoryTable (ending with key word “Table”). They all have an empty string value. These column names signify the locations in which multiple Child Level records are to be plugged in during XML document creation.

InfoPath Form XML Document

Based on the discussion above, the logic steps for creating, populating and submitting an InfoPath form XML document may be summarized below:

  • Execute the stored procedure sp_InfoPathDB_FormLibrary_Info_select to pull out InfoPath form and library attributes;
  • Start an XML document and add document header information;
  • Execute the stored procedure sp_InfoPathDB_Employee_Select_TopLevel to pull out an employee’s contact information, the single Top Level record;
  • Loop through its columns to build XML data elements using column names as XML element names and column values as XML element values;
  • When the key word “Table” is encountered in a column name, execute one of the stored procedures sp_InfoPathDB_Education_Select_ChildLevel or sp_InfoPathDB_JobHistory_Select_ChildLevel to pull out Child Level records;
  • Build XML data elements for a repeating section recursively.
  • Upon completion, upload the XML document to a SharePoint form library.

Let’s take a look at how the above logic steps are implemented. Listing 7 shows the source code of CreateInfoPathForm(), a function that does the work.

Three parameters are passed in: connectionString –accessing a SQL database, formID –retrieving InfoPath form and library attributes and employeeID –retrieving an employee’s contact, education and job history information.

Listing 7:

    public static void CreateInfoPathForm(string connectionString, int formID, int employeeID)
    {
        //Retrieve form infomation from database, such as namespace, form library URL, form template URL, processing instruction, etc.
        DataRow FormLibraryInfo = GetDataTable(connectionString, "sp_InfoPathDB_FormLibrary_Info_select", formID).Rows[0];
        //all Urls from database are relative. Become a full url by adding a site root url in front of them.
        string SiteRootUrl = FormLibraryInfo["SiteRootUrl"].ToString();
        string FormLibraryUrl = SiteRootUrl + FormLibraryInfo["FormLibraryUrl"];
        //there are three namespaces in an InfoPath form template
        string FormNamespace_my = FormLibraryInfo["FormNamespace_my"].ToString();
        string FormNamespace_xsi = FormLibraryInfo["FormNamespace_xsi"].ToString();
        string FormNamespace_xd = FormLibraryInfo["FormNamespace_xd"].ToString();
        string FormTemplateUrl = SiteRootUrl + FormLibraryInfo["FormTemplateUrl"].ToString();
        string FormProcessingInstruction1 = FormLibraryInfo["FormProcessingInstruction1"].ToString();
        string FormProcessingInstruction2 = FormLibraryInfo["FormProcessingInstruction2"].ToString();
        //select top level data for an employee into a DataTable
        DataTable TopLevelTable = GetDataTable(connectionString, "sp_InfoPathDB_Employee_Select_TopLevel", employeeID);
        DataRow Employee = TopLevelTable.Rows[0];
        //get a unique file name the the InfoPath form
        string FormFileName = Employee["LastName"].ToString() + "_" + Employee["FirstName"].ToString() + "_" + Employee["ID"].ToString() + ".xml";
        byte[] InfoPathXmlData = null;
        using (MemoryStream MStream = new MemoryStream())
        {
            using (XmlTextWriter XmlTxWriter = new XmlTextWriter(MStream, Encoding.UTF8))
            {
                XmlTxWriter.Formatting = Formatting.Indented;
                //start writing Xml
                XmlTxWriter.WriteStartDocument();
                // Create the required processing instructions (refer to the template.xml file generated by InfoPath)
                //manifest.xsf in the instruction should be replaced by form template url
                XmlTxWriter.WriteProcessingInstruction("mso-infoPathSolution", @FormProcessingInstruction1.Replace("manifest.xsf", FormTemplateUrl));
                XmlTxWriter.WriteProcessingInstruction("mso-application", @FormProcessingInstruction2);
                // Create the XML start data element:  with three namespaces
                XmlTxWriter.WriteStartElement("my", "myFields", FormNamespace_my); 
XmlTxWriter.WriteAttributeString("xmlns", "xsi", null, FormNamespace_xsi); XmlTxWriter.WriteAttributeString("xmlns", "xd", null, FormNamespace_xd);       
//create data elements 
          CreateXmlElement(connectionString, employeeID, XmlTxWriter, TopLevelTable, FormNamespace_my, false, ""); 
//end the root data element 
                XmlTxWriter.WriteEndElement();
                //end the XML doc
                XmlTxWriter.WriteEndDocument();
                XmlTxWriter.Flush();
                XmlTxWriter.Close();
            }
            InfoPathXmlData = MStream.ToArray();
            MStream.Close();
        }       
        // Upload the newly created InfoPath form to a form library on a SharePoint Server
        if (InfoPathXmlData != null && InfoPathXmlData.Length != 0)
        {
            using (WebClient client = new WebClient())
            {
                // Set the credentials to be used for upload to SharePoint
                client.Credentials = CredentialCache.DefaultCredentials;
                // Upload the newly created form to a SharePoint form library
                client.UploadData(@FormLibraryUrl + FormFileName, "PUT", InfoPathXmlData);
            }
        }
    }
 

The stored procedure sp_InfoPathDB_FormLibrary_Info_select is first executed to return a DataRow - FormLibraryInfo that holds form and library attributes. The results are assigned to local variables for later use. An employee’s contact information, the Top Level record, is then pulled into a DataTable - TopLevelTable by executing the stored procedure sp_InfoPathDB_Employee_Select_TopLevel. A unique FormFileName, required when uploading a form XML document to a SharePoint form library, is formed by concatenating the employee’s last name, first name and employee ID. A byte array InfoPathXmlData is declared in order to hold the XML document that is created using an XmlTextWriter writing to a MemoryStream. Let’s examine the code inside using (XmlTextWriter){..} code block that builds the XML document.

   //start writing Xml
   XmlTxWriter.WriteStartDocument();

The WriteStartDocument() method is called to start an XML document.

   // Create the required processing instructions (refer to the template.xml file generated by InfoPath designer)
   //manifest.xsf in the instruction should be replaced by form template url
   XmlTxWriter.WriteProcessingInstruction("mso-infoPathSolution", @FormProcessingInstruction1.Replace("manifest.xsf", FormTemplateUrl));
   XmlTxWriter.WriteProcessingInstruction("mso-application", @FormProcessingInstruction2);
 

Two processing instructions are added to the document by calling the WriteProcessingInstruction() method. The file name “manifest.xsf” in the “mso-infoPathSolution” processing instruction is replaced by a form template full URL on a SharePoint server. If the URL is missing, the InfoPath form uploaded to the SharePoint server will not work properly.  

    
// Create the XML start data element:  with three namespaces 
XmlTxWriter.WriteStartElement("my", "myFields", FormNamespace_my); XmlTxWriter.WriteAttributeString("xmlns", "xsi", null, FormNamespace_xsi); XmlTxWriter.WriteAttributeString("xmlns", "xd", null, FormNamespace_xd);

The root data element: <my:myFields> is created and three InfoPath form namespaces are added. 

    //create data elements
    CreateXmlElement(connectionString, employeeID, XmlTxWriter, TopLevelTable, FormNamespace_my, false, "");
 

Next, the function CreateXmlElement() is called to pull employee’s data from a SQL database and to create all data elements inside the root <my:myFields>. This is a recursive call to be discussed shortly.

  //end the main data element 
   XmlTxWriter.WriteEndElement();
   //end the XML doc
   XmlTxWriter.WriteEndDocument();
   XmlTxWriter.Flush();
   XmlTxWriter.Close();

Finally, WriteEndElement() method closes the root data element </my:myFields>, and WriteEndDocument() method completes the XML document.

The generated XML document in the MemorySream - MStream is then placed into the byte array InfoPathXmlData that is uploaded to a SharePoint form library using the code below. 

    // Upload the newly created InfoPath form to a form library on a SharePoint Server
    if (InfoPathXmlData != null && InfoPathXmlData.Length != 0)
    {
        using (WebClient client = new WebClient())
        {
            // Set the credentials to be used for upload to SharePoint
            client.Credentials = CredentialCache.DefaultCredentials;
            // Upload the newly created form to a SharePoint form library
            client.UploadData(@FormLibraryUrl + FormFileName, "PUT", InfoPathXmlData);
        }
    }

 
In the above process, XML data elements are constructed by the function CreateXmlElement(), as presented in Listing 8.

Listing 8:

   //Loop through rows and columns of a DataTable to write Xml elements and data.
    public static void CreateXmlElement(string connectionString, int employeeID, XmlTextWriter XmlTxWriter, DataTable tbl, string myNamespace, bool requireRowElement, string childTableName)
    {
        foreach (DataRow r in tbl.Rows)
        {
            if (requireRowElement)//write row start element, like <educationrow> or <jobhistoryrow>
            {
                XmlTxWriter.WriteStartElement("my", childTableName.Replace("Table", "") + "Row", myNamespace);//row starts   
            }
            foreach (DataColumn col in tbl.Columns)//Write data elements inside a row
            {
                //for repeating items, the main proc contains an empty column with a column name ending with a key word "Table"
                //for example, EducationTable, JobHistoryTable, etc.
                //when the key word is detected, create a child DataTable for the repeating items.
                //in this example, there are only two child tables: EducationTable and JobHistoryTable. 
                //The child stored procedure name is hard-coded using "if" statement
                //For a real business applicatin, there may be many child tables. Their proc names may be saved into database 
                //and retrieved based the the column name that ends with key word Table.
                if (col.ColumnName.IndexOf("Table") > -1)//col.ColumnName is a child table name
                {
                    string ChildStoredProc = "";
                    if (col.ColumnName == "EducationTable")
                    {
                        ChildStoredProc = "sp_InfoPathDB_Education_Select_ChildLevel";
                    }
                    else
                    {
                        ChildStoredProc = "sp_InfoPathDB_JobHistory_Select_ChildLevel";
                    }
                    //place child level data for a repeating section into a table
                    DataTable ChildTable = GetDataTable(connectionString, ChildStoredProc, employeeID);
 
                    //write the start element with key word "Table", for example <educationtable> or <jobhistorytable>
                    XmlTxWriter.WriteStartElement("my", col.ColumnName, myNamespace);
                    //recursively call to write rows and their data elements
                    CreateXmlElement(connectionString, employeeID, XmlTxWriter, ChildTable, myNamespace, true, col.ColumnName);
                    //Write end element for table. For example </jobhistorytable></educationtable>
                    XmlTxWriter.WriteEndElement();
                }
                else
                {
                    //create each individual data element
                    CreateXmlElement(XmlTxWriter, r, col, myNamespace);
                }
            }
            if (requireRowElement)//write row end element, like </jobhistoryrow> or 
            {
                XmlTxWriter.WriteEndElement();//row ends   
            }
        }
    }

Employee data is passed into the function as a DataTable. When the function is executed for the first time, an employee’s contact information, a single data row contained in the DataTable - TopLevelTable is passed in. This is the time that the Top Level record is processed. As mentioned before, this row includes two special columns, named as EducationTable and JobHistoryTable that signify the locations to plug in Child Level Records. Code below illustrates the function call. 

CreateXmlElement(connectionString, employeeID, 
           XmlTxWriter, TopLevelTable, FormNamespace_my, false, "");
 
The last two parameters: requireRowElement=false and childTableName=”” indicate that this is a call to process the Top Level record and no row element (<EducationRow> or <JobHistoryRow>) needs to be written. The function Loops through each column and calls an overload of CreateXmlElement() as shown in Listing 9 to build XML data elements one at a time. If the key word “Table” is detected in a column name, it is time to retrieve multiple Child Level records for a Repeating Section. Depending on the column name, a Child Level stored procedure, sp_InfoPathDB_Education_Select_ChildLevel or sp_InfoPathDB_JobHistory_Select_ChildLevel, is executed and the results are placed into a DataTable, declared as ChildTable.

    //place child level data for a repeating section into a table
    DataTable ChildTable = GetDataTable(connectionString, ChildStoredProc, employeeID);
   //write the start element with key word "Table", for example <educationtable> or <jobhistorytable>
    XmlTxWriter.WriteStartElement("my", col.ColumnName, myNamespace);
    //recursive call to write rows and their data elements
    CreateXmlElement(connectionString, employeeID, XmlTxWriter, ChildTable, myNamespace, true, col.ColumnName);
    //Write end element for table. For example </jobhistorytable>
    XmlTxWriter.WriteEndElement();
Here, a recursive call to the function CreateXmlElement() takes place, where the ChildTable contains multiple education or job history records, the parameter requireRowElement is set to true indicating that a “row” element <EducationRow> or <JobHistoryRow> needs to be written. The parameter childTableName holds the column name “EducationTable” or “JobHistoryTable”, with which the row element <EducationRow> or <JobHistoryRow> can be created by replacing the word “Table” with the word “Row”.

Listing 9:

    //write a set of basic Xml elements. Each includes start element + data + end element
    public static void CreateXmlElement(XmlTextWriter XmlTxWriter, DataRow r, DataColumn col, string myNamespace)
    {
        string StrValue = r[col.ColumnName].ToString();          
        XmlTxWriter.WriteStartElement("my", col.ColumnName, myNamespace);
        //for date field, if no value, add an attribute of nil
        if (col.ColumnName.Length > 4 && col.ColumnName.ToLower().Substring(col.ColumnName.Length - 4) == "date" && StrValue == "")
        {
            XmlTxWriter.WriteAttributeString("xsi", "nil", System.Xml.Schema.XmlSchema.InstanceNamespace, "true");
        }
        XmlTxWriter.WriteString(StrValue);
        XmlTxWriter.WriteEndElement();
    }
 
Listing 9, the overload of CreateXmlElement(), simply writes a start XML element, an end XML element, and a value in between.

That is how an InfoPath form XML document is created, populated and uploaded to a SharePoint form library. For complete code listing, please refer to the class file InfoPathFormXml.cs in the demo application.

Demo Application  

A web site project is prepared for download using Visual Studio 2012 and SQL Express 2008 R2. Right click on default.aspx in Solution Explorer and select View in Browser to run the application. Since a SharePoint server is not available, the InfoPath XML document generated in the application cannot be uploaded but simply displayed. In addition, an XML file, named as EmpployeeForm.xml, is also generated and saved in the folder XmlFile. The XML file gives a better presentation when opened in the Visual Studio IDE because of its color code and indentation

In order to display the XML document on default.aspx page, an overload for CreateInfoPathForm() is added to the InfoPathFormXml class, that returns an XML string through a reference variable InfoPathXmlGenerated.

The InfoPath form template file - EmployeeInfo.xsn is also included in the project, and its source file collection is located at the sub folder XsnSourceFiles.

Summary

This article illustrates a technique for dynamically creating, populating and submitting an InfoPath form to a SharePoint form library. A challenge in the process is to create an XML document that is dynamically populated with personalized data from a SQL database. A proper naming convention for InfoPath data fields associated with column names return from the SQL database plays an important role. Coding tricks handling XML in a Repeating Section on the form are also introduced.

Although only one form template is involved in the discussion, by adding a supporting data structure in database to provide form attributes and to map stored procedure names to different templates, the dynamic nature of the technique could be well extended.

References 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tomz_KV
Web Developer
United States United States
Web & Database Developer. Design and implement web and database applications utilizing Microsoft and other development tools.

Comments and Discussions

 
QuestionHelp Me : I can't open the Form in SharePoint Form Library PinmemberBlackRoseIR15-Sep-13 22:03 
AnswerRe: Help Me : I can't open the Form in SharePoint Form Library PinmemberTomz_KV16-Sep-13 3:05 
GeneralRe: Help Me : I can't open the Form in SharePoint Form Library PinmemberBlackRoseIR16-Sep-13 21:43 
GeneralRe: Help Me : I can't open the Form in SharePoint Form Library PinmemberTomz_KV17-Sep-13 2:31 
GeneralRe: Help Me : I can't open the Form in SharePoint Form Library PinmemberBlackRoseIR17-Sep-13 20:21 
QuestionUnable to attach Database File to SQLExpress 2008 R2 PinmemberJohn_Toronto12-Sep-13 5:40 
AnswerRe: Unable to attach Database File to SQLExpress 2008 R2 PinmemberTomz_KV12-Sep-13 9:58 
GeneralRe: Unable to attach Database File to SQLExpress 2008 R2 PinmemberJohn_Toronto12-Sep-13 12:50 
GeneralRe: Unable to attach Database File to SQLExpress 2008 R2 PinmemberTomz_KV13-Sep-13 5:19 
QuestionAuto-population from another Sharepoint list Pinmemberdatasmith6924-Jul-13 12:29 
AnswerRe: Auto-population from another Sharepoint list PinmemberTomz_KV25-Jul-13 2:33 
QuestionThis detailed walk-through and sample code has been a tremendous help. Pinmembernanodata30-Apr-13 12:43 
AnswerRe: This detailed walk-through and sample code has been a tremendous help. PinmemberTomz_KV1-May-13 10:16 
GeneralRe: This detailed walk-through and sample code has been a tremendous help. Pinmembernanodata2-May-13 7:18 
GeneralMy vote of 5 Pinmembernanodata30-Apr-13 12:17 
QuestionDo you have code examples that will work with Visual 2008 and Infopath 2007 and SharePoint 2007? PinmemberD.Steffen1-Apr-13 14:21 
AnswerRe: Do you have code examples that will work with Visual 2008 and Infopath 2007 and SharePoint 2007? [modified] PinmemberTomz_KV2-Apr-13 2:45 
GeneralRe: Do you have code examples that will work with Visual 2008 and Infopath 2007 and SharePoint 2007? [modified] PinmemberD.Steffen2-Apr-13 6:10 
GeneralRe: Do you have code examples that will work with Visual 2008 and Infopath 2007 and SharePoint 2007? PinmemberTomz_KV2-Apr-13 9:00 
QuestionDon't use sp_ in stored procedure names PinadminChris Maunder31-Mar-13 9:37 
SR0016: Avoid using sp_ as a prefix for stored procedures[^]
cheers,
Chris Maunder
 
The Code Project | Co-founder
Microsoft C++ MVP

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.1411023.1 | Last Updated 12 Sep 2013
Article Copyright 2013 by Tomz_KV
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid