Copy a SharePoint list to a repeating table in InfoPath using XSLT

Learn how to copy data from a SharePoint list to a repeating table in InfoPath by using XSLT and C# code to transform the data from a secondary data source and store it in the Main data source.

ADVERTISEMENTS

Problem

You have a SharePoint list that contains a date column and you want to be able to copy the data from the SharePoint list to a repeating table on an InfoPath form.

SharePoint date columns once included in an InfoPath secondary data source become strings that have a different date format than the date formats used in InfoPath. And since the nodes of the secondary data source to the SharePoint list are locked once the SharePoint list is bound to InfoPath, you are unable to change the data type on the fields of the SharePoint list from within InfoPath.

Solution

Use an XSLT to transform the SharePoint date format into a format that InfoPath recognizes as a date and copy the entire contents of the SharePoint list to a repeating table in InfoPath.

Discussion

You can copy data from a SharePoint list to a repeating table in InfoPath using XSLT and C# code as follows:

  1. In SharePoint, use an existing SharePoint list or create a new one that has a date column in it. Here we'll use the SharePoint list displayed in the following figure:

    SharePoint list containing a date column
    Figure 1. SharePoint list containing a date column.
  2. In InfoPath, create a new Blank form template.
  3. Add a data connection to the SharePoint list from step 1. Here we'll select the Title, FullName, and DueDate fields to be included in the data connection, and call the data connection SimpleList.

    The XML for the secondary data source to the SharePoint list in InfoPath resembles the following:

    The XML of a secondary data source to a SharePoint list in InfoPath
    Figure 2. The XML of a secondary data source to a SharePoint list in InfoPath.

    Note: You can retrieve the XML of the secondary data source by using the following C# code:

    string xml = DataSources["SimpleList"].CreateNavigator().OuterXml;

    This is the XML that you'll have to transform; the source XML. Note how the dates are displayed in the format yyyy-MM-dd HH:mm:ss. You'll have to transform these dates into an InfoPath date format such as yyyy-MM-dd and that can be used in a Date Picker control.

  4. Add a Repeating Table control to the InfoPath form template and change the field in the third column of the repeating table into a Date Picker control in the repeating table.

    The XML for the repeating table resembles the following:

    The XML of a repeating table in InfoPath
    Figure 3. The XML of a repeating table in InfoPath.

    Note: You can retrieve the XML for the repeating table in the Main data source of the InfoPath form template by using the following C# code:

    string xml = MainDataSource.CreateNavigator().SelectSingleNode(
      "//my:group1", NamespaceManager).OuterXml;

    This is the XML that you'll have to create; the destination XML. Note that the repeating table is empty at this point in time and by default contains only one row. group2 is the node that repeats. You'll have to copy Title from the SharePoint list to field1, FullName to field2, and DueDate to field3.

  5. Construct an XSLT stylesheet as shown in Figure 4, save the file as transform.xsl, and add it as a Data Connection (select Receive data and XML document in the data connection wizard when creating the data connection) to the InfoPath form and name the secondary data source transform:
    XSLT used to transform data from the SharePoint list into data for the repeating table in InfoPath
    Figure 4. XSLT used to transform data from the SharePoint list into data for the repeating table in InfoPath.

    This XSLT loops through the SharePoint list and creates a group2 for each item in the SharePoint list. While looping through the SharePoint list, it copies the values from the Title, FullName, and DueDate SharePoint list columns into the field1, field2, and field3 nodes of the group2 node.

    Note: This XSLT uses the substring() XSLT function on the DueDate field in the SharePoint list to extract the first 10 characters from the date string. Furthermore, the dfs namespace in the XSLT was copied from the source XML and the my namespace from the destination XML. You'll have to substitute the my namespaces in the XSLT with the namespace for your own InfoPath form template.

  6. Add a Button control to the InfoPath form template.
  7. Add the following C# code in the Clicked event handler of the button:

    // Retrieve the xsl to use to transform the SharePoint list
    DataSource ds = this.DataSources["transform"];
    XslCompiledTransform trans = new XslCompiledTransform();
    trans.Load(ds.CreateNavigator());

    using (MemoryStream ms = new MemoryStream())
    {
      // Transform the SharePoint list and save the XML into the MemoryStream
      trans.Transform(DataSources["SimpleList"].CreateNavigator(), null, ms);

      // Rewind the MemoryStream
      ms.Position = 0;

      // Load the MemoryStream into an XPathDocument
      XPathDocument doc = new XPathDocument(ms);

      // Get a reference to the group1 node
      XPathNavigator group1 =
        MainDataSource.CreateNavigator().SelectSingleNode(
        "//my:group1", NamespaceManager);

      // Replace the group1 node with the transformed XML
      group1.ReplaceSelf(doc.CreateNavigator());
    }

    Note: The following additional using statements are required for the code to work:

    using System.IO;
    using System.Xml.Xsl;

  8. Save your work, build the project, and test the InfoPath form.

You should now have a fully functional form, so that when you click the button, the data in the SharePoint list is transformed and copied over to the repeating table in the Main data source of the InfoPath form.
Repeating table in InfoPath displaying transformed SharePoint list data
Figure 5. Repeating table in InfoPath displaying transformed SharePoint list data.

 
 Subscribe for updates via RSS or Email

Copyright: This article may not be used on web sites (whether personal or otherwise), copied, disseminated, altered, printed, published, broadcasted, or reproduced in any way without an expressed written consent of S.Y.M. Wong-A-Ton. The techniques demonstrated in this article may be used within any Microsoft InfoPath project. This article is provided without any warranties. Copyright for this article is non-transferrable and remains with the author, S.Y.M. Wong-A-Ton.

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners
InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013
InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners
InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010
InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers
InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services


Related InfoPath Articles:

ADVERTISEMENTS

InfoPath 2013 Cookbook: 121 Codeless Recipes for Beginners

InfoPath 2013 Cookbook 2: 121 Codeless Recipes for SharePoint 2013

InfoPath 2010 Cookbook: 101 Codeless Recipes for Beginners

InfoPath 2010 Cookbook 2: 101 Codeless Recipes for SharePoint 2010

InfoPath 2010 Cookbook 3: 101 Code Recipes for C# Developers

InfoPath 2010 Cookbook 4: 101 Code Recipes for VB Developers

InfoPath 2010 Cookbook 5: Integrating InfoPath with Excel and Excel Services