Transform data from a secondary data source using XSLT

Applies to: InfoPath 2003

Use XSLT to transform the data stored within a secondary data source.



You have a SharePoint list that stores the first name, last name, and a calculated field as the full name, which is composed from the first and last name fields. When you add a data connection to this SharePoint list from within InfoPath, the calculated field for the full name does not appear in the list of fields you can select to use in the secondary data source, while you would still like to use this field as the display text in a drop-down list box that is bound to your secondary data source.


Add an extra non-calculated field to your SharePoint list that will show up in InfoPath and use an XSL transformation to produce the calculated value and populate the extra field of the secondary data source.


You can accomplish this functionality as follows:

  1. You have a SharePoint list called AtlantaBravesTeam with the columns LastName, FirstName, and FullName, where FullName is a calculated field that uses the formula
    =IF(FirstName<>"",CONCATENATE(LastName,", ",FirstName),LastName)
  2. Add an extra field to the SharePoint list and call it ExtraFieldForInfoPath. This field will remain empty in SharePoint.
  3. Create a New Blank Form in InfoPath.
  4. Create a Data Connection to the SharePoint list, select the LastName, FirstName, and ExtraFieldForInfoPath to be included in the data connection, and call the data connection AtlantaBravesTeam.
  5. The XML data for the SharePoint list used in this solution resembles the following:
    <dsf:AtlantaBravesTeam LastName="Cormier" FirstName="Lance" ExtraFieldForInfoPath=""/>
    <dsf:AtlantaBravesTeam LastName="Francoeur" FirstName="Jeff" ExtraFieldForInfoPath=""/>
    <dsf:AtlantaBravesTeam LastName="Giles" FirstName="Marcus" ExtraFieldForInfoPath=""/>
    <dsf:AtlantaBravesTeam LastName="Jones" FirstName="Chipper" ExtraFieldForInfoPath=""/>
    Note: Namespace declarations have been left out to increase clarity.
  6. Add a Table with Title to the InfoPath form.
  7. Add a Drop-Down List Box control to the table and bind it to the Data Connection for the SharePoint list. Use the LastName field as the Value and the ExtraFieldForInfoPath as the Display name.
  8. Add a Button control to the table and give it the Label Refresh List.
  9. Modify the table and controls to resemble the following figure:

    Figure 1. The InfoPath form in design mode.
  10. Add the following XSLT stylesheet as a Data Connection (select Receive data and XML document in the data connection wizard when creating the data connection) to the form and call it Transform:

    Figure 2. XSLT used to transform data in the secondary data source.
    This stylesheet uses the FirstName and LastName attributes of each dsf:AtlantaBravesTeam node in the secondary data source to generate the value for the ExtraFieldForInfoPath attribute:
    <xsl:attribute name="ExtraFieldForInfoPath">
    <xsl:value-of select="@LastName"/>
    <xsl:if test="@FirstName != ''">, </xsl:if>
    <xsl:value-of select="@FirstName"/>
  11. Go to Tools > Programming > On Load Event... to add an OnLoad event handler to the form. Add the following code to the OnLoad event handler:
    This code ensures that whenever the form is opened, the data in the secondary data source will be transformed to the format specified by the stylesheet.
  12. Switch back to InfoPath, double-click on the Button control to open its Properties dialog box.
  13. Click on the Edit Form Code... button to add an event handler for the button. Add the following code to the event handler for the button:
    where AtlantaBravesTeam is the name of the data connection to the SharePoint list. The first line in this code refreshes the data stored within the secondary data source (just in case items were added, edited, or deleted from within SharePoint), while the second line transforms the data in the secondary data source to the format specified by the stylesheet.
  14. Add the following JScript function that performs the XSL transformation and replacement of the data in the secondary data source at the end of the script.js file:
    function transformATeamList()
    // Load in the data from the secondary data source
    var aTeamDOM = XDocument.GetDOM("AtlantaBravesTeam");

    // Load in the stylesheet to perform the transformation
    var xsltDoc = XDocument.GetDOM("Transform");

    // Transform the data in the secondary data source
    var transformedXml = aTeamDOM.transformNode(xsltDoc);

    // Load the transformed data into a new XML document
    var newATeamDOMDataFieldsDOM = XDocument.CreateDOM();

    // Set the namespaces used in the aTeamDOM

    // Retrieve the dfs:dataFields node of the aTeamDOM
    var aTeamDOMDataFieldsNode = aTeamDOM.selectSingleNode("/dfs:myFields/dfs:dataFields");

    // Replace the dfs:dataFields node of the sec. data source with the new dfs:dataFields
    aTeamDOMDataFieldsNode.parentNode.replaceChild(newATeamDOMDataFieldsDOM.documentElement, aTeamDOMDataFieldsNode);

You should now have calculated values for the ExtraFieldForInfoPath field within the secondary data source showing up in your drop-down list box. These values were generated through the use of an XSL transformation of the original data from a secondary data source.


Related InfoPath Articles:


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. 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.