This example guides you through shredding, which is extracting fields from your form into SQL tables. This is often used for enabling reports on form information with SQL reporting services. To start mapping XML documents into SQL tables we will create a very simple form with only a few fields, a database that we can map these fields into, and a mapping to describe how the two are related.
1. Design a new form
a. Create a new, blank form.
Figure 1
b. Open the Data Source Task Pane, and rename the root node myFields to ContactManagement.
Figure 2
c. Right click the ContactManagement node and click Add. Name the node OwnerName. Make sure Type is set to Field (element) and Data Type is set to Text (string). Click OK.
d. Repeat Step (c), creating a new node called OwnerPhone.
e. Repeat Step (c), creating a new node called OwnerEmail. See Figure 3 for details on the form’s schema.
f. Add the three nodes to the view with appropriate labels.
g. Insert a Submit button that submits the data to the web service DBXLDocumentService. For more information refer to the document called How to Configure a Form to Submit to DBXL WebService.
h. Save the form.
Figure 3: The form schema
Figure 4: The Completed form design
Several of the parameters to the DBXL web service methods require values to be pulled from XML in the form. Since we do not want to compromise the purity of our form’s main DOM with these extra meta nodes, we will create a secondary DOM to store them in and then add this DOM as a secondary data source to the form.
2. Add the secondary DOM.
a. Open a text editor and create a new XML file with the following content:
<?xml version="1.0" encoding="UTF-8"?>
<Util>
<DBXL>
<DocTypeName>ContactManagement</DocTypeName>
<Name></Name>
<Author></Author>
<Description></Description>
</DBXL>
</Util>
Note: The value of the DocTypeName element should correspond to the Document Type that you plan to submit to. In this case, we’re calling it ContactManagement.
b. Save this file and name it Util.xml.
c. Switch back to the InfoPath Designer, and select Data Connections from the Tools menu.
d. Click Add, select Receive Data, and then click Next.
e. Select XML Document, click Next, and then browse to the Util.xml that was saved above. Click Next.
f. Select Include the data as a resource file in the form template or template part and click Next.
g. Click Finish, and then Close.
Now that the form has been created we need to create a database that we can shred the form into. For this exercise the database will only need one table, but in later exercises we will shred the XML into several tables, with several different levels of relationships.
Note that the table we are about to create in the target database has a total of four columns, but the form that we are shredding only has three nodes. The reason the extra node is required is so that DBXL can keep track of which rows in the DB table correspond to which Document in the DBXL database. The easiest way to add this column is to mark the column as an IDENTITY. Two very important characteristics that IDENTITY columns have are the ability to automatically increment the value with each insertion of a new row, and the ability to anonymously reference this column.
If there are no keys defined for a certain table, DBXL assumes that there is an IDENTIY column present and takes the appropriate actions. Generally, the IDENTITY column is marked as the primary key for a table, but this is not required. See MSDN Documentation for more information on this property.
3. Design the Database.
a. Open SQL Server Management Studio and connect to the database you wish to shred into, or create a new one for the purposes of this tutorial.
b. Create a new table in this database with a table named Owner, and four columns, ID, Name, Phone, and Email. Assign the following data types to each column:
ID: int
Name: nvarchar(1024)
Phone: nvarchar(1024)
Email: nvarchar (1024)
c. Open the Column Properties dialog box for the ID column, and scroll down until you find the Identity Specification section. Expand this section, and make sure (Is Identity) is marked as Yes. See Figure 5 for details.
d. Make sure the Allow Nulls option is checked for each of the columns, except ID, and then save the table.
Figure 5: Specifying a column as IDENTITY In SQL Server Management.
Now that the form and the database schemas have been implemented, we are ready to begin making a mapping to enable shredding for this form.
4. Create the Mapping
a. Open DAT and click the New Configuration button.
b. Name the mapping ContactManagement, and then complete the Data Source field with the appropriate string. Note that this string is what the web service uses to connect to the target database, so make sure that it works from this perspective. In our example case, the string will take the following form: Integrated Security=SSPI;Data Source=<servername>;Initial Catalog=<database>
c. In the Form Template field, click the file attachment control, browse to the form template file that was saved earlier, and attach it to the form.
d. Click on Save button and then click on OK button for the dialog message that confirms that changes were saved.
e. Click on the Database tab.
f. Now, insert one row in the Database Map section. This will be the row we use to map a Table, so Table is automatically selected from the dropdown.
g. The left side of the Database Map section deals with the target database schema, and the right side deals with the XML schema. Either type in the name of the table, Owner, or press the browse button to open the Database schema task pane. To insert a name from the database schema task pane, simply double click on it. The database schema taskpane establishes a remote connection to the SQL database to derive its schema using the connection string provided in the Data Source field.
h. Insert the XPath for the root-node in the Node Path field. Use the browse button to browse the schema of the form you have attached to this document type.
i. Insert another row, but this time make sure that it is indented slightly to the right of the first row that was inserted.
j. Choose Column as the type.
k. This row in the mapping corresponds to the column in the Owner table, so click on the browse button to select it from the database schema.
l. For the Node Path field, you click the browse button and select the node my:OwnerName from the taskpane. Note that the node path from one item to its children is relative and you only need to specify the path from the parent to the child for DBXL to be able to find the correct node. Also note that any valid XPath will work for these fields, so if necessary, you may employ any kind of special XPath. For more information about XPath, see this tutorial: http://www.w3schools.com/xpath/default.asp
m. Insert another two rows to map the Phone and Email columns.
n. Click the Save button at the top of the form to save the Document Type to DBXL and click on OK button for the confirmation dialog.
o. Click on Catalog to return to the catalog view.
Figure 6: The completed mapping for the Contact Management form.
The mapping between DBXL and SQL has now been created. The next section will discuss more advanced shredding techniques.
After completing the above tutorial on shredding with DBXL you were probably left wondering how we would handle shredding nested XML tables into relational tables in the database. In this tutorial we will go over how to accomplish this scenario. Please note that the above tutorial must be completed before starting this section.
5. Add the extra nodes to the form.
a. Open the form that was created in the previous tutorial in the InfoPath design mode.
b. Click above the Submit button to place the insertion point. Open the Controls task pane and insert a repeating table with three columns.
c. Open the Data Source task pane and notice that when the repeating table was inserted the appropriate nodes were added to the XML Schema. Rename these nodes as specified below.
group1 = Contacts
group2 = Contact
field1 = Name
field2 = Phone
field3 = Email
d. Label the columns in the view by entering the field name into the column headers.
e. Save the form so that the XSN file contains the changes.
Figure 7: The extended form schema.
6. Add the extra table to the relational database.
a. Open SQL Server Management Studio and connect to the database you wish to shred into.
b. Create a new table in this database named Contacts with four columns as specified below:
OwnerId: int
Name: nvarchar(1024)
Phone: nvarchar(1024)
Email: nvarchar(1024)
c. Create a new foreign key relationship between the Contacts table and the parent Owner table. This step is not required for the form to work, but it is a widely accepted best practice to maintain data purity.
d. Make sure that the Allow Nulls column is checked for all but the OwnerId columns and then save the table.
Figure 8: The contact table schema
Now that the form and the database schemas have been updated we must update the mapping to accommodate for these additions.
7. Update the mapping.
a. Open DAT and click Edit for the ContactManagement document type.
b. Update the form template by attaching the latest version of the XSN file to the Form Template field in the General tab and click on the Save button.
c. Click on the Database tab.
d. Directly below the last column mapping, click the Insert Table or Column link to insert a row nested below the Owner row.
e. Select Table from the dropdown list, use the browse button to select Contacts for the table name, and insert my:Contacts/my:Contact for the NodePath.
f. Click the Insert Foreign Key link, type OwnerId for the Column Name, and then select ID for the Foreign Object Name.
g. Insert a mapping for each of the Name, Phone, and Email columns.
h. Save the updated mapping.
Figure 9: The updated mapping.
Now that all of the schemas have been updated, the form is ready to shred the newest additions.