Where & how is information saved in a SQL server DB?

[Nick Monsour]

Hi,

When I create a form in InfoPath with SP1 and deploy them through WSS,
where in the SQL Server does the information get stored? This is if I
am creating the form from nothing. My company wants to be able to use
SQL Reporting to report off the information stored in the SQL Server
that is captured in InfoPath forms. If this can be figured out then I
will no longer need to create the SQL database structure (which has
proven to be complicated when submitting).

Thank you,
Nick

 

[Michelle]

Are you asking where in the Sharepoint DB is the XML file being stored? Can
you clarify? Thanks.

 

[Nick Monsour]

Ok I may be making an assumption. Let's say I create a form with no
data source, where is the data stored?
Can it be directed to store in a SQL server db? And if yes can it be
reported off of?
I have several forms to create and making the SQL Server DB has proven
to be troublesome at best. Mostly because of the submit issue with
multiple many to one relationships.

 

[Nick Monsour]

Ok I may be making an assumption. Let's say I create a form with no
data source, where is the data stored?
Can it be directed to store in a SQL server db? And if yes can it be
reported off of?
I have several forms to create and making the SQL Server DB has proven
to be troublesome at best. Mostly because of the submit issue with
multiple many to one relationships.
I almost forgot. These forms are published on a WSS site but not to be
stred there since you can't eliminate users from being able to modify
them or delete them. That is why I am being forced to use SQL Server.

 

[Andrew Watt [MVP - InfoPath]]

Ok I may be making an assumption. Let's say I create a form with no
data source, where is the data stored?
Can it be directed to store in a SQL server db? And if yes can it be
reported off of?
I have several forms to create and making the SQL Server DB has proven
to be troublesome at best. Mostly because of the submit issue with
multiple many to one relationships.

Nick,

This is slightly a "If you want to get there, don't start from here"
answer.

You can store information from a form template in SQL Server.

Once you do that, assuming you have SQL Server Reporting Services, you
can create reports from the data.

You should look into using Web services with SQL Server when using
InfoPath. Hagen Green demo'ed some relevant stuff in his webcast the
other day.

One of the issues you may be hitting (assuming you are using WSS as
your original question hinted) is that under the hood there is MSDE
not SQL Server. You cannot (technically, I believe) use Reporting
Services with MSDE on WSS (or anywhere else) without their being a
separate licensed instance of full SQL Server. The metadata from the
reports has to be in the full SQL Server, I believe.

Andrew Watt
MVP - InfoPath

 

[Michelle]

The data entered into an InfoPath form becomes an XML file which can be saved
as a document in a form library or document library in Sharepoint. That
document is then stored in its entirety in the Sharepoint DB. You can also
save the file to a fileshare or email it and so on. It's just an XML file.

If you want to store the data captured in the XML file into a DB you will
need to shred the data into the appropriate tables and columns. One way
would be to write a webservice that takes an XML string and then parses it
into stored procedures parameters to add or update rows in the database.
There are several other ways to interact with a DB using InfoPath.

There was a webcast on Tuesday that covered Database connectivity in
InfoPath which may be of use to you. You should be able to view it as On
Demand from the following link soon:
http://www.microsoft.com/seminar/events/webcasts/ondemand.mspx