3 Ways to integrate InfoPath with SQL Server

Filed under: InfoPath 2010

Learn in which 3 ways you can combine and integrate SQL Server with InfoPath when it comes to extracting data from a SQL Server database or storing data in a SQL Server database.

ADVERTISEMENTS

SQL Server has been around for a very long time. I've been working with SQL Server since version 6.5.

And because SQL Server has been around for a while, it has also been adopted by many businesses. So not surprisingly, many businesses will have data stored in SQL Server databases.

InfoPath is like the "new kid on the block". While adoption has been slow, it has been gaining traction in the past couple of years thanks to its bigger brother, SharePoint. This means that more and more businesses are looking for ways to use and integrate InfoPath into their business processes.

But what are the integration possibilities when it comes to SQL Server and InfoPath together?

This post is intended to give you an overview of how you could combine these two Microsoft technologies with each other. I’ve found 3 ways; here they are…

1. Using SQL Server as a container for InfoPath forms

Because InfoPath forms are XML files, you can store them in a SQL Server database table either as typed or untyped XML, since SQL Server offers an XML data type that can be used on table columns.

Typed XML would be XML that is validated by SQL Server (according to a defined and registered XML schema within SQL Server) when data is stored in the database, while untyped XML would not be validated by SQL Server.

Note that you could also theoretically speaking store entire InfoPath forms in database columns that have one of the SQL data types that support large amounts of data.

Let’s also not forget the fact that once the XML of InfoPath forms has been stored in SQL Server, you can use SQL statements (XQuery) to query that XML.

2. Using SQL Server as a data provider for InfoPath forms

Data that is stored in a SQL Server database can be retrieved from within InfoPath either through a direct data connection to a database table or through a web service that can connect and retrieve data from SQL Server. Either way, SQL Server would then be providing data to InfoPath forms.

Within InfoPath itself, once you have made a connection to SQL Server, you can use the data from SQL Server for several things such as for example populating drop-down list boxes, or even validating fields against data stored in SQL Server.

3. Using SQL Server as an InfoPath form generator

A SQL Server database generally does not contain only one table, but several tables that are related to each other. Such relational data can be retrieved through normal SQL statements that make use of one or more JOIN clauses.

The result-set can then be used to generate the XML that would represent an InfoPath form. So basically, you would be generating InfoPath forms inside of SQL Server by using normal SQL statements or SQL Server stored procedures (see for example Create an InfoPath form using a SQL statement in the InfoPath 2010 Cookbook 3 Club).

A final step would then be to use SQL Server Integration Services (SSIS) for example to export the resulting XML of the InfoPath forms to a destination of choice, which could be for example a location on disk or another database. The possibilities are endless really.

 


Related Posts

 

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.

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