InfoPath Dev

IA, I've spent the last 2 weeks researching InfoPath + Sharepoint, reading MSDN & blogs, watching webcasts, searching forums, ad infinitum. From my own forum experience, I know it's a pain when someone fires off questions that have been answered 100 times without even doing the courtesy of a search. I'm posting this after I feel I've done my own fair bit of due dilligence. ;)

I'm specing out an enterprise data and content management application. What I need is the ability to collect (sometimes complex) information on various entities (ie customers, partners, products) from both internal departments inside the company (rich client) and external partners (data collected via the web browser)... then blow this out into:

1) Generate documents for various departemtns
2) A SQL database that drives an ecommerce site (product info)
3) Reporting
4) A legacy ERP app (don't ask lol)

My goal is to determine if I can leverage Infopath/Sharepoint/related technologies to accomplish my goal, or if the application should just be built from scratch with SQL/.Net web forms.

It's looking like I can definitely use infopath 2007 and Sharepoint 3.0/forms services to do this, but there are a few things still keeping me skeptical. From those who have done it before... an honest opinion would be appreciated: Are these features "easy", "will never work" or "it can be done, but lord you don't know what you're getting into. Run screaming."


1) Can I host forms on a sharepoint site using the infopath/browser single design, and collect that data directly into a SQL 2005 db?

2) How is user access level/roles support inside infopath (ie this user can only see their data, these fields are readonly, this user group can do anything), esp the browser forms? It doesn't look like it's supported in the designer, but can I feasibly code it? (ie this part of this form is hidden from this user, readonly for this user, etc)

3) Is it feasible to code some change management/audit trail functions into such a setup? ie keeping track of data history, who changed what from what to what, when? I know it's certainly possible, but will getting such functionality with Infopath/forms service sin front create so much extra work Imight as well code it from scratch?

4) Does anyone have experience with saving Infopath's XML form data and schemas into the new SQL2005 XML datatypes, and all that that implies? I've found lots of info on both standalone concepts, but if there is anyone out there doing both together yet they have evading my intertron searching skills.



Infopath/forms services/Sharepoint look to be very useful and are pretty close to what I'm wanting to do... but am I getting myself into more integration trouble than it's worth with my special requirements?


Thanks in advance, I know it's a meaty post for an Infopath newb. ;)

For bonus points, some of the form data collected will have attached documents.

For example, data on a product might have care instructions, Materials Safety sheets, or creative assets... in formats like pdf, doc, ppt, & gif/jpg associated with it. Using infopath/forms server to collect the data via web browser/rich client, store it in SQL2005, and store associated documents in Sharepoint would be very powerful... and looks like Infopath/Forms Services/Sharepoint could do it.

Wishful thinking?

Hello Mr. Galloway and welcome to the forums!

I would like to say first and foremost that your dedication and diligence on looking for answers for yourself is beyond outstanding! Thank you and I would be more than happy to offer what advice I may.

It seems like you have grand ambitions for this project, but I think it can be done with InfoPath, SharePoint, and with the help of our middle-tier Database Accelerator (or, DBXL). DBXL will solve problems 2, 3, and 4 in a way that InfoPath and SharePoint could not and make solving problem number 1 much easier.

The main difference you are going to experience between these two methods of implementation is flexibility. If you go with an all-custom app you will have the utmost of flexibility, but at the cost of having to build and maintain a considerably larger amount of code. This code will also be much more complex as you will have to plan abstractions for mapping the XML data into SQL so that you don't rewrite a lot of code. Otherwise, you could use our DBXL web service which has done all of the abstractions for you and not write a single line of code. InfoPath/SharePoint can be a surprisingly powerful base for a large solution, but sometimes you have to find workarounds to what would normally be a trivial task if using ASP.NET or similar technologies. Patrick would be able to tell you more about the past projects as I am relatively new to the company, but they have created large-scale solutions with over 65,000 users with InfoPath and SharePoint technologies.

As for the browser-based forms - I have not worked too much with this but if you approach it rationally as it seems you do to most things in your life you should be able to figure out an acceptable solution. Again, the main point here is that you will not have as much flexibility. An ASP.NET form can be made to do basically anything you desire without too much effort, but the InfoPath-generated web form has even less functionality than the InfoPath rich client (of course).

There will be some things that are more difficult to accomplish using InfoPath/SharePoint, but in the end I think you will have a much more manageable and much more elegant of a solution.

Oh, I almost forgot, here's a link to our DBXL:http://www.qdabra.com/proddetail.asp?prod=QDBXL1

Hi DasGalloway and welcome to our forum!

I would like to repeat Matt's praise. It is always a pleasure to respond to someone who has done a lavel of due diligence but you seem to be particularly deep in your awareness of the technologies as well as your sense of the challenges.

1) Generating documents - this one's a little tricky. Assuming the documents are end-points to a process, you can quite easily use the print to Word or Export to Excel features to do this. IP2007 has more export features, but bottom line is that it's not easy to do. It can be done, but it's not easy. IP2007 is better than IP2003 (for example, inserting data fields in footers and headers which was not supported in IP2003).

2) SQL DB - yes. Cheapest way is to buy our Web service. We are just starting a new campaign for version 2 to give away a BASIC version for free (same as the current Developer Edition). We have many customers on this and it's a great way to accelerate deployment, cut costs, and reduce maintenance headaches. Send us e-mail for a free demo of the accelerator suite.

3) Reporting - you can use SharePoint for simple view reporting (cheapest); InfoPath itself can be used as a custom reporting solution assuming you don't need a lot of end-user pivoting and that render speed isn't a huge concern; typically we find that Excel is the best rich client reporting environment. Pulling data into Excel using either Excel Services or just simple data mapping is very powerful. SQL Reporting Service is the high end solution which is not that hard to configure and gives you oodles of features but doesn't have the out-of-box interactivity of Excel. All of these approaches are good. Reporting can be costly but assuming you have the data in your database it isn't a huge technical hurdle.

4) Legacy ERP - key question is whether or not the legacy app needs to modify data. If so, and you have to support database writes from two clients - InfoPath and the Legacy ERP, you won't be able to use our accelerator. The Qdabra DBXL Web servcice assumes it is the only writer of data. This is important for a) permissions, b) data synchronization and locking, and c) XML datatype fidelity. If the Legacy ERP is just a consumer and not a producer, it's relatively straightforward to create a DTS transform from SQL database to the ERP database or use another approach (multiple submits), but the key is whether it needs to write to the main DB or not.

To respond to your questions:

1) Form Server has limitations (filtering, taskpane, ...) but is a huge step forward for MS. Using Form Server with a Web service on top of a SQL 2005 database is definitely doable especially if the Web service is out-of-the-box. Double hop authentication is a bit tricky (you can use the new Single Sign On stuff to simplify it). The big challenge is SharePoint has it's own data repository. We solve this problem with our Database Accelerator using a webpart that functions similar to a SharePoint list view to show documents, promoted properties, and allow sorting or filtering on that data. The documents are stored in the database using the Web service. You can do this on your own and store a copy on SharePoint to get the cheap view support with promoted properties but always open the doc from the DB. However, due to some inflexibility in how InfoPath integrates with SQL, unless you have a Web service, you will face challenges synchronizing, deleting, and locking data in SQL - not to mention schema consistency since InfoPath will bind to SQL if you connect directly to it and hence changes in the client or server schemas will require significant rework. Once again, our Web service solves this by always saving the entire document (as XML in the DB) and shredding out the data to SQL tables. This allows for a more robust solution in that the DB schema can change without causing the solution to stop submitting. You go in and fix the mapping and reshred the documents that were saved in the meantime, or vice versa if the client schema changes.

2) Access Level - for an InfoPath-only solution, everyone needs to be logged in to the same domain. Most solutions will use Active Directory Web service (which we bundle with DBXL). SharePoint 2007 provides better support for document-level permissions but it isn't dynamic. I.e. if the access on a document changes as the result of a submit or workflow state change, SharePoint doesn't support dynamically changing the permissions set without writing code. Once again, DBXL fills this gap and allows you to set permissions for groups or individuals. Depending on read or write permissions, the InfoPath form should then switch to a view or show a section that allows it to enable the appropriate permissions for the user.

3) Audit Trail - DBXL supports full Audit Trail, but that's per document not per field. Per field change tracking is doable but requires more work. Emergency Rooms typically need per level change/audit tracking. We've done this before, but it's some work. Easier to do on top of DBXL (just submit per change). If all you need is per document auditing, that's easy with DBXL, or you could implement your own auditing in SQL.

4) SQL 2005 XML. The issue is the schema. SQL needs to validate the XML that is submitted. There are some limitations around XML schema datatypes (I think maybe xsd:any) but in general the problem is the validation. If the schema changes, you have to update the associated schema in SQL but that then invalidates previous schames. We prefer a looser coupling so we store the XML as a string and shred it out using xpaths. When the shredding fails, we log the event and let the admin catch it, fix it, and reshred later without disrupting the submit process. We can do this since the document is saved in the DB. We associate version info with documents so that when the client schema changes we can decide whether to auto-update the data or leave it with the previous version (typically the latter is the default). Because the IP client itself validates the extra level of schema validation that SQL XML types want to enforce is a nuisance and mainly causes problems when the schema changes or with certain types that aren't well supported.

5) (BONUS) Attachments. Depends on your data architecture and the network connection speed for clients. If the attachments are not processed (i.e. data islands) and the connection is fat pipe, I wouldn't worry. You can save the attachments in SQL and inject links into the form, or save the attachments in the form's native BASE64 encoding (which leads to slower form opens). Links are preferred.

That's all for now. Feel free to drop us a line if you want a DBXL demo.

Alrighty, I'm back.

Patrick, I never posted thanks for your response above, so here goes... Thanks!


At this point, I've got a proof of concept working...

1) IP form -> web service -> SQL 2005 (XML Data type)
2) Tested manipulation with Xquery, etc
3) Browser Hyperlink -> IP form (query) -> web service -> SQL 2005 (XML) -> web service (result data) -> IP form

Pardon the "forum notation". ;)

This provides me with a very powerful and flexible SQL-based doc management system, given one interface I can manage N form templates, security, workflow, version control, you name it. (yes, sharepoint provides much of that, but it's not in use here)

Basically, form data will be stored in the XML while it's in the "working" states of the work flow, then "published" to relational tables when the "working" activities are complete. All changes to the form data itself will be stored in the XML, allowing us to roll back to previous data versions of a "form". Pretty slick =)

The only remaining major "trick" to solve for has become managing mapping the SQL XML -> SQL relational tables... the shredding of my XML to multiple relational tables... and the unshredding of tables back to XML (as posted above I knew this was going to be a gotcha)


Now, we can write our own shredder/unshredder (what's the verb opposite of shredding? unshredding? Pulping? XMLifying? lol)... but I'm looking for a tool to mmanage mapping and shredding for me.

The DBXL ap seems to be meant for sitting between infopath and SQL... not for sitting between SQL and SQL. Would it work for what we're trying to do here? If you just have XML in SQL (IP or not) can it handle the relationships between that XML and tables?


Thanks again for the help guys!

Oops... as an added gotcha... other processes will be modifying the data in the relational tables after it has been published from XML (requirement). To handle that, I'll need to set up a comparator that can generate a new XML "version" from the relational tables and slap it into the SQL XML table... so it has to go both ways.

DBXL requires it's the only editor of the relational table data... it must know everything. Sounds like it might not work then... hrmm

Hi John,
I've been using "re-hydrate" as the reverse of shred. Our DBXL tool could be modified to handle your SQL to SQL case, but I believe the SQL XML Type to SQL Relational still is not as flexible as what we advocate because the SQL XML Type requires an XML schema associated with it to validate the XML. However, your XML schema is bound to changes and when it does you will have to update the schema in SQL that validates it. When you do that, you run the risk of breaking old forms and of having a work stoppage, since you have to update the form and then require people to open a new instance of InfoPath to get the update. Why not just store the XML as a string and not worry about validation in SQL? After all, InfoPath does that for you anyway. This allows a more flexible shredding regime. You can change the client schema and shredding may fail, but your data still gets submitted and saved to SQL. In addition, you can handle multiple form versions. Our tool allows you to do full history and revision tracking and you can configure it so that a previous form opens with the previous version of the template - using the old schema - or in a new version - which forces an upgrade event on the schema. The result is that you have less maintenance issues for your customers and the reporting and downstream processes which consume the Relational schema can live with some latency on the mapping update. Using DBXL you can have two InfoPath forms (with different schemas) shredding to the same table. Of course, you could do this with SQL XML Type too but the problem is that you can't update the schema in place because people may have InfoPath forms open on the client and then when they submit, it breaks. Our solution allows you to update in place without affecting concurrent users.

As for the requirement that "other processes" modify the tables - this won't work with DBXL. One of the holy grails of DBXL is that it does not require any DB changes - i.e. you can map it to any pre-existing table without having to modify it. When you throw in the "multiple writers" requirement, this requires that you keep lock states for all rows so that you make sure users (and applications) don't overwrite one another's data. Keeping lock state in the tables is a lot of work and will significantly complicate your solution. It's just not a good design. We keep lock information in a table accessed by the Web service. So long as everyone uses the Web service, the solution works very well. As soon as some app tries to modify the shredded tables directly, however, the data fidelity is lost - that's because shredding is one way. We are not reconstituting or rehydrating the XML from SQL. Doing so would cause problems because SQL data types are different from XML and it's a lot of work to create the same XML schema especially when the client schema can change. There is a simple solution - just have the other apps call the Web service instead of writing directly to the SQL tables.

One more issue with SQL XML Type is that it doesn't support as many XML schemas as InfoPath. Specifically, I believe there are issues validating XHTML and xsd:any structures (but you should check into this yourself).

Anyway, sounds like you have a challenging design. I wouldn't do what you are doing. Definitely DBXL isn't going to support that as it was designed with the above considerations in mind.

Happy trails!

Patrick-

Excellent points, after more consideration I'm reworking the design to move/replace the direct editing of shredded tables. That design creates two data stores (XML and tables) with neither being fundamentally authoritative, and all that that implies. Instead, the XML will be authoritative, and the shredded relational tables will only be a snapshot... no insert/updates to them. That's a better design from a business process perspective, as it enforces a editor/pubslisher model, even for mass updates.

We won't be using SQL schemas, they're just not compatible enough with infopath's schema support yet.

Sent you an email, give me a call. DBXL seems to fit much better in the new, better, design.

A quick tech-check shows that the default behavior of XML datatype columns in SQL 2005 is to accept any valid XML string. As long as the string is valid XML it will be stored in the column without any validation done. However, if you want to add validation via a schema you can do that with the CREATE XML SCHEMA COLLECTION statement, and then associate this schema with the column.

Also, a complete list of the limitations on schemas and the XML datatype in general can be found here:
http://msdn2.microsoft.com/en-us/library/ms190665.aspx

Further reference:
http://davidhayden.com/blog/dave/archive/2006/04/11/2909.aspx
http://msdn2.microsoft.com/en-us/library/ms176009.aspx

Exactly Matt... the impassse I came up against was that SQL 2005 doesn't support the full XSD specification... while Infopath does. Therefore, you can't just take a schema directly from IP and load it into a schema collection, you have to "translate" it to be SQL 2005 friendly. While not impossible to do (or automate) it added a layer of management and complexity I wanted to avoid.

I decided to simply store straight XML in SQL and handle the schema enforcement on the interface side via IP. If it gets through IP, then I know it follows my validation rules, and SQL becomes a "dumb data store" with straight up XML.

Thanks for the reply!

Sounds good to me! It does make sense to have all your data validation in one place (InfoPath). I know you will find DBXL to be of great help to your endeavor!