infopath and sql xml data type

[Jody Loewer]

We are "newbies" with "some" development background who are interested in
developing solutions where we:

• use Infopath as our primary data entry tool
• store the resulting Infopath data in an XML-type column in SQL Server
2005
• use Sharepoint as the user interface structuren for the remainder of the
solution
• use XQuery against the stored Infopath data in SQL Server 2005 for
complex searches

We do not want to store the Infopath data in Sharepoint because:

• the performance issues associated with a large number of documents in
a sharepoint folder
• there are too many tags sets to realistically use the tag --> column
promotion feature of Sharepoint
• Sharepoint does not support the concept of repeating structures within
its column promotion feature
• any Sharepoint search will have a time delay between "crawls" - we
need to do a true query against the data

We think that we are missing some fundamental concept. We can retrieve
sample data from the SQL XML-type column via a data connection (using either
a direct query or a stored procedure; with or without XQuery) but Infopath
always sees that data as pure text that happens to contain tags - Infopath
never connects the character sequences with the concept of an XML structure.

What are we missing? Are there any articles that directly deal with the
integration of Infopath with the XML-type of SQL Server 2005? Or, is there a
specific chapter in a book that we should be reading?

Thank you for any input.

 

[jb]

Jody,

I am using a similar approach using sql xml because I found the performance
of the built in data connections sufficient for only a 2 - 3 tables at most.
The sqlxml application peformance is about 10x superior to using the built in
data connections in addition to operating over the Internet. We are also
storing the documents in a sharepoint library in addition to the database.

The "best practice" fabrikam code from Microsoft recommends transforming the
SQL friendly xml to infopath friendly xml and vice versa. Most of the
samples are great. The one that was the best example of this for us was the
InfoPath expense form. We simplified the example a great deal, removed the
signatures complexity, etc..

The best practice code that I have is from the fabrikam best practices is
found at

http://www.microsoft.com/downloads/...f6-f6e1-4b18-89fc-31d7cfd74e19&DisplayLang=en

There are many "best practices". The two most notable as it relates to your
question are the schema mapper tool which will generate an xsd schema
containing the relationship among multiple tables for sql server not a
trivial task. Secondly, the SQLXMLFPC Component which will handle the CRUD
Operations to sql. The example also show how to dynamically generate a form
from the database, etc.

The most notable gap was the xsl to tranlate sqlxml to infopath friendly
xml. In our case, we designed the form using standard InfoPath xml
tehniques/datasource. We then took that xml and mapped it to sqlxml format
using the biztalk mapper. The biztalk mapper generates xsl when you test a
map.

That is a chapter in the book we have spent on this. The good news is that
using this architecture, Infopath now appears to be working well. There are
many unscalable features / paths with Infopath and Sharepoint so performing a
solid proof of concept is recommended.

jb