Upon occasion you may need to copy external data into form fields bound to the form's Main Data Source. In this sample for InfoPath 2007, we will create a form with nested repeating tables and using code copy our external data to the appropriate tables. Our scenario is a selection of vehicles, grouped by type (Sports Cars, Luxury Cars, etc) with an array of dealers for each specific vehicle. All files used in this sample are available here: Copy Repeating External Data Into a Form
Sample Form Schema
The sample form schema for the main data source looks like this:

Secondary Data Sources
We need two XML files that we will add as secondary data connections. The first is the Vehicles.xml:
<?xml version="1.0" encoding="utf-8"?>
<vehicles>
<vehicle>
<TypeId>1</TypeId>
<VehicleType>Sports Cars</VehicleType>
<VehicleId>1</VehicleId>
<VehicleYear>1968</VehicleYear>
<VehicleMake>Chevrolet</VehicleMake>
<VehicleModel>Corvette</VehicleModel>
<VehicleColor>Red</VehicleColor>
</vehicle>
<vehicle>
<TypeId>1</TypeId>
<VehicleType>Sports Cars</VehicleType>
<VehicleId>2</VehicleId>
<VehicleYear>2004</VehicleYear>
<VehicleMake>Dodge</VehicleMake>
<VehicleModel>Viper</VehicleModel>
<VehicleColor>Red</VehicleColor>
</vehicle>
<vehicle>
<TypeId>1</TypeId>
<VehicleType>Sports Cars</VehicleType>
<VehicleId>3</VehicleId>
<VehicleYear>2007</VehicleYear>
<VehicleMake>Ferrari</VehicleMake>
<VehicleModel>599 GTB</VehicleModel>
<VehicleColor>Red</VehicleColor>
</vehicle>
<vehicle>
<TypeId>1</TypeId>
<VehicleType>Sports Cars</VehicleType>
<VehicleId>4</VehicleId>
<VehicleYear>1978</VehicleYear>
<VehicleMake>Pontiac</VehicleMake>
<VehicleModel>Trans Am</VehicleModel>
<VehicleColor>Black</VehicleColor>
</vehicle>
<vehicle>
<TypeId>2</TypeId>
<VehicleType>Classic Cars</VehicleType>
<VehicleId>5</VehicleId>
<VehicleYear>1924</VehicleYear>
<VehicleMake>Ford</VehicleMake>
<VehicleModel>Model T</VehicleModel>
<VehicleColor>Black</VehicleColor>
</vehicle>
<vehicle>
<TypeId>3</TypeId>
<VehicleType>Trucks</VehicleType>
<VehicleId>6</VehicleId>
<VehicleYear>1990</VehicleYear>
<VehicleMake>Dodge</VehicleMake>
<VehicleModel>Ram</VehicleModel>
<VehicleColor>Blue</VehicleColor>
</vehicle>
<vehicle>
<TypeId>3</TypeId>
<VehicleType>Trucks</VehicleType>
<VehicleId>7</VehicleId>
<VehicleYear>2006</VehicleYear>
<VehicleMake>Ford</VehicleMake>
<VehicleModel>F250</VehicleModel>
<VehicleColor>Pewter</VehicleColor>
</vehicle>
<vehicle>
<TypeId>3</TypeId>
<VehicleType>Trucks</VehicleType>
<VehicleId>8</VehicleId>
<VehicleYear>2008</VehicleYear>
<VehicleMake>GMC</VehicleMake>
<VehicleModel>3500</VehicleModel>
<VehicleColor>Green</VehicleColor>
</vehicle>
<vehicle>
<TypeId>4</TypeId>
<VehicleType>Luxury Cars</VehicleType>
<VehicleId>9</VehicleId>
<VehicleYear>2009</VehicleYear>
<VehicleMake>Rolls-Royce</VehicleMake>
<VehicleModel>Phantom Coupe</VehicleModel>
<VehicleColor>Silver</VehicleColor>
</vehicle>
<vehicle>
<TypeId>4</TypeId>
<VehicleType>Luxury Cars</VehicleType>
<VehicleId>10</VehicleId>
<VehicleYear>2009</VehicleYear>
<VehicleMake>Cadillac</VehicleMake>
<VehicleModel>CTS</VehicleModel>
<VehicleColor>Tan</VehicleColor>
</vehicle>
</vehicles>
The second is Dealers.xml:
<?xml version="1.0" encoding="UTF-8"?>
<dealers>
<dealer>
<VehicleId>1</VehicleId>
<DealerId>dlr_cw</DealerId>
<DealerName>Cal Worthington</DealerName>
</dealer>
<dealer>
<VehicleId>1</VehicleId>
<DealerId>dlr_kp</DealerId>
<DealerName>Kirkland Pontiac</DealerName>
</dealer>
<dealer>
<VehicleId>2</VehicleId>
<DealerId>dlr_pd</DealerId>
<DealerName>Penrose Dodge</DealerName>
</dealer>
<dealer>
<VehicleId>2</VehicleId>
<DealerId>dlr_sf</DealerId>
<DealerName>Sommerset Ford</DealerName>
</dealer>
<dealer>
<VehicleId>2</VehicleId>
<DealerId>dlr_wd</DealerId>
<DealerName>Wilconson Dodge</DealerName>
</dealer>
<dealer>
<VehicleId>4</VehicleId>
<DealerId>dlr_kp</DealerId>
<DealerName>Kirkland Pontiac</DealerName>
</dealer>
<dealer>
<VehicleId>5</VehicleId>
<DealerId>dlr_sf</DealerId>
<DealerName>Sommerset Ford</DealerName>
</dealer>
<dealer>
<VehicleId>6</VehicleId>
<DealerId>dlr_pd</DealerId>
<DealerName>Penrose Dodge</DealerName>
</dealer>
<dealer>
<VehicleId>6</VehicleId>
<DealerId>dlr_wd</DealerId>
<DealerName>Winconson Dodge</DealerName>
</dealer>
<dealer>
<VehicleId>7</VehicleId>
<DealerId>dlr_af</DealerId>
<DealerName>Allen Ford</DealerName>
</dealer>
<dealer>
<VehicleId>7</VehicleId>
<DealerId>dlr_pjf</DealerId>
<DealerName>Penny-James Ford</DealerName>
</dealer>
<dealer>
<VehicleId>7</VehicleId>
<DealerId>dlr_sf</DealerId>
<DealerName>Sommerset Ford</DealerName>
</dealer>
<dealer>
<VehicleId>8</VehicleId>
<DealerId>dlr_kp</DealerId>
<DealerName>Kirkland Pontiac</DealerName>
</dealer>
<dealer>
<VehicleId>8</VehicleId>
<DealerId>dlr_spbg</DealerId>
<DealerName>Seaview Pontiac Buick GMC</DealerName>
</dealer>
<dealer>
<VehicleId>9</VehicleId>
<DealerId>dlr_bhrr</DealerId>
<DealerName>Beverly Hills Rolls-Royce</DealerName>
</dealer>
<dealer>
<VehicleId>9</VehicleId>
<DealerId>dlr_vrr</DealerId>
<DealerName>Vegas Rolls-Royce</DealerName>
</dealer>
<dealer>
<VehicleId>10</VehicleId>
<DealerId>dlr_fc</DealerId>
<DealerName>Florida Cadillac</DealerName>
</dealer>
<dealer>
<VehicleId>10</VehicleId>
<DealerId>dlr_kp</DealerId>
<DealerName>Kirkland Pontiac</DealerName>
</dealer>
<dealer>
<VehicleId>10</VehicleId>
<DealerId>dlr_spbg</DealerId>
<DealerName>Seaview Pontiac Buick GMC</DealerName>
</dealer>
<dealer>
<VehicleId>10</VehicleId>
<DealerId>dlr_vsc</DealerId>
<DealerName>Vegas Strip Cadillac</DealerName>
</dealer>
</dealers>
As you can see, there is a one to many relationship between the xml files. Each vehicle may have any number of dealers.
Sample Form Construction
Create a new form with a schema like that shown in the Sample Form Schema section. Add the two XML files as resource files:
- Select Resource Files from the Tools menu
- Select the Add button in the Resource Files window
- Browse the Vehicles.xml file and select the OK button
- Repeat for the Dealers.xml and select the OK button in the Resource Files window to close
Now create two secondary data connections using the resource files:
- Select Data Connections from the Tools menu
- Select the Add button in the Data Connections window
- Select Create a new connection to:
- Select Receive data
- Select the Next button
- Select XML document for the source of the data
- Select the Next button
- Select the Resource Files button
- Select the Dealers.xml file that you added to the Resource Files in the section above
- Select the OK button
- Select the Next button
- Select the Finish button
- Repeat these steps to add a data connection to the Vehicles.xml
In your form, add a drop-down list box bound to the selectedType node in the main data source. The values for the drop-down should be provided by the VehicleType nodes in the Vehicles data connection:
- Under List box entries in the properties for the drop-down list box, select Look up values from an external data source
- Select Vehicles from the Data source drop-down list box
- Select the button next to Entries to open the Select a Field or Group window
- Select the VehicleType node
- Select the OK button
- Select Show only entries with unique display names
- Select the OK button
You can preview your form and check that your drop-down is correct:

Add a repeating table connected to Vehicle in the main data source, and a field that will display dealer information in a repeating section in that table.


Add a button with the label "Add Rows". We will use it to run our code for copying our data. The finished form should look something like this:

Form Code
Right-click the Add Rows button, and, under Button Properties, select the Edit Form Code button.
Microsoft Script Editor or VSTA will open, depending on what programming language your form is using (this can be changed under Tools/Options/Programming).
In JScript, the following code for the button OnClick event will copy data from Vehicles.xml and Dealers.xml into the tables on your form:
// Get the selected vehicle type. var selectedType = XDocument.DOM.selectSingleNode("/my:myFields/my:selectedType").text;
// If no form was selected notify the user. if(selectedType == "") { XDocument.UI.Alert("Please select a vehicle type first."); return; } // Get the list of vehicles per the selected vehicle type. var vehicles =XDocument.GetDOM("Vehicles").selectNodes("/vehicles/vehicle[VehicleType = '" + selectedType + "']");
// If nothing was found, then return; there will be no items to insert. if(vehicles == null) return; // Get the Vehicle insertion point. var vehicleList = XDocument.DOM.selectSingleNode("/my:myFields/my:Vehicles");
// Add the rows to the main DOM. var vehicle; while(vehicle = vehicles.nextNode()) { try { // Get this vehicle's values from the secondary DOM. var vehicleId = vehicle.selectSingleNode("VehicleId").text; var year = vehicle.selectSingleNode("VehicleYear").text; var make = vehicle.selectSingleNode("VehicleMake").text; var model = vehicle.selectSingleNode("VehicleModel").text; var color = vehicle.selectSingleNode("VehicleColor").text;
// Build a string representing a main DOM vehicle nodeset using the values from the secondary DOM. var vehicleXml = "<my:Vehicle xmlns:my='http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57'>" + "<my:Year>" + year + "</my:Year>" + "<my:Make>" + make + "</my:Make>" + "<my:Model>" + model + "</my:Model>" + "<my:Color>" + color + "</my:Color>" + "<my:Dealers>";
// Get the list of Dealers for this vehicle. var dealers = XDocument.GetDOM("Dealers").selectNodes("/dealers/dealer[VehicleId = '" + vehicleId + "']");
// Add the dealer rows to the XML. var dealer; while(dealer = dealers.nextNode()) { var dealerId = dealer.selectSingleNode("DealerId").text; var dealerName = dealer.selectSingleNode("DealerName").text; vehicleXml += "<my:Dealer>" + "<my:DealerId>" + dealerId + "</my:DealerId>" + "<my:DealerName>" + dealerName + "</my:DealerName>" + "</my:Dealer>"; }
vehicleXml += "</my:Dealers></my:Vehicle>"
// Load new vehicle string into a temporary XML DOM. var domNewRow = XDocument.CreateDOM(); domNewRow.validateOnParse = false; domNewRow.async = false; domNewRow.loadXML(vehicleXml); domNewRow.setProperty("SelectionNamespaces", 'xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57%22'); // Insert the new vehicle into the main DOM. vehicleList.appendChild(domNewRow.selectSingleNode("my:Vehicle")); } catch(ex) { XDocument.UI.Alert("Could not insert vehicle row.\n\nError message:\n" + ex.Message); } } |
And, for C#, you can use this code in your button's Clicked event:
// Get the selected vehicle type. string selectedType = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:selectedType", NamespaceManager).Value;
// If no form was selected notify the user. if (selectedType.Equals("")) { MessageBox.Show("Please select a vehicle type first."); return; }
// Get the list of vehicles per the selected vehicle type. XPathNodeIterator vehicles = this.DataSources["Vehicles"].CreateNavigator().Select("/vehicles/vehicle[VehicleType = '" + selectedType + "']", NamespaceManager);
// If nothing was found, then return; there will be no items to insert. if (vehicles.Count == 0) { return; }
//Get the Vehicle insertion point. XPathNavigator vehicleList = MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:Vehicles", NamespaceManager);
//Add the rows to the main DOM. foreach (XPathNavigator vehicle in vehicles) { try { // Get this vehicle's values from the secondary DOM. string vehicleId = vehicle.SelectSingleNode("VehicleId", NamespaceManager).Value; string year = vehicle.SelectSingleNode("VehicleYear", NamespaceManager).Value; string make = vehicle.SelectSingleNode("VehicleMake", NamespaceManager).Value; string model = vehicle.SelectSingleNode("VehicleModel", NamespaceManager).Value; string color = vehicle.SelectSingleNode("VehicleColor", NamespaceManager).Value;
// Build a string representing a main DOM vehicle nodeset using the values from the secondary DOM. string vehicleXml = "<my:Vehicle xmlns:my='http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-06-03T00:09:57'>" + "<my:Year>" + year + "</my:Year>" + "<my:Make>" + make + "</my:Make>" + "<my:Model>" + model + "</my:Model>" + "<my:Color>" + color + "</my:Color>" + "<my:Dealers>";
// Get the list of Dealers for this vehicle. XPathNodeIterator dealers = this.DataSources["Dealers"].CreateNavigator().Select("/dealers/dealer[VehicleId = '" + vehicleId + "']", NamespaceManager);
// Add the dealer rows to the XML. foreach (XPathNavigator dealer in dealers) { string dealerId = dealer.SelectSingleNode("DealerId", NamespaceManager).Value; string dealerName = dealer.SelectSingleNode("DealerName", NamespaceManager).Value;
vehicleXml += "<my:Dealer>" + "<my:DealerId>" + dealerId + "</my:DealerId>" + "<my:DealerName>" + dealerName + "</my:DealerName>" + "</my:Dealer>"; }
vehicleXml += "</my:Dealers></my:Vehicle>";
//Load new vehicle string into a temporary XML document XmlDocument tempDoc = new XmlDocument(); tempDoc.LoadXml(vehicleXml);
// Insert the new vehicle into the main DOM. XPathNavigator newVehicle = tempDoc.CreateNavigator().SelectSingleNode("my:Vehicle", NamespaceManager); vehicleList.AppendChild(newVehicle); } catch (Exception ex) { MessageBox.Show("Could not insert vehicle row.\n\nError message:\n" + ex.Message); } } |
Your form is now ready to try. Preview it, select a vehicle type from the drop-down list box, select the Add Rows button, and your repeating tables should populate with the correct data from your secondary data sources.
