11.3. Databases If the product catalog were in a relational database rather than an XML document, we could still perform similar validation and form completion functions. InfoPath has built-in support for Access and SQL Server databases, but also has a variety of techniques for supporting other databases. 11.3.1 Access and SQL Server For example, suppose we have an Access database, catalog.mdb, which has a table named CATALOG, shown in Figure 11-3. Figure 11-3. The CATALOG table 
This process can be seen in action using the order_db.xsn example form. First, we set up a secondary data source for the database. This process is identical to the process for setting up a database as a primary data source, described in 9.3.6.4, "Starting from a database", on page 206. In our example, we specified a secondary data source named catalogdb that pointed to the catalog.mdb database, and chose all the table columns. Tip  | In general, you'll want to set up a secondary data source for each type of query that you will be performing on the data, rather than for each database or table. Doing so allows you to select only the tables, columns and joins that are relevant to the query, reducing the amount of data returned and ensuring that the data is joined appropriately. |
Once a secondary data source is set up, we can use a very similar script to what we used when selecting from a standalone XML document. The only changes required are to the lookupCatalog function, which is shown in Example 11-4. Example 11-4. Script to check product number against database function lookupCatalog(prodNum) { var catdbDom = XDocument.GetDOM("catalogdb"); catdbDom.setProperty("SelectionNamespaces", 'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/' + 'ado/dataFields"'); var nameNode = catdbDom.selectSingleNode( "//d:CATALOG[@NUM='" + prodNum + "']/@NAME"); if (nameNode) return nameNode.nodeTypedValue; }
InfoPath will convert the Access results into XML that can then be queried using the same methods we used to query the catalog XML document. An example of the XML output of the query is shown in Example 11-5. Example 11-5. Output of the catalog database query <dfs:myFields xmlns:dfs= "http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d= "http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"> <dfs:dataFields> <d:CATALOG NUM="334" NAME="Ten-Gallon Hat" DEPT="ACC"/> <d:CATALOG NUM="356" NAME="White Linen Blouse" DEPT="WMN"/> <d:CATALOG NUM="522" NAME="Deluxe Golf Umbrella" DEPT="ACC"/> <d:CATALOG NUM="999" NAME="Miscellaneous" DEPT="NA"/> </dfs:dataFields> </dfs:myFields>
The output document has a CATALOG element for each row in the CATALOG table, and each column is represented as an attribute. As this is a different structure from that of the catalog.xml file in Example 11-1, we use a different XPath expression in line 8 of Example 11-4 to retrieve the name. In addition, because the CATALOG element is in its own namespace, we also need to use a prefix in the XPath expression. In order to map the prefix to the namespace, we call the setProperty method to set the SelectionNamespaces property, as shown in lines 4 through 6 of Example 11-4. 11.3.2 Other databases The built-in database support described in the previous section will work for both Access and SQL Server databases. There are a number of techniques for accessing other kinds of databases using InfoPath, such as: -
building a SOAP Web service that accesses the data and returns it as XML (for example, using ADO.NET) -
writing scripts that access the database through any data access API, such as ADO, or a custom API provided by a vendor -
importing and exporting data from a database as XML files (most relational databases support this) and consuming the files in InfoPath[2] [2] Refer to the documentation for your DBMS product to determine your options for accessing data as XML. | 
