Submitting XHTML to a SQL database

[David Fries]

I saw that this has been answered before, but I am still a bit stuck.

It seems that there are three approaches to solving this problem, correct?
1) Submitting via a webservice
2) Having a Rich Text box in the form (which isn't one of the data fields),
which copies over its contents to a text box (which is part of the data
fields) prior to submitting.
3) Submit to SQL as an XML data-typed column

Here's what I've tried with the 3 different approaches:
1) For the web service approach, does this mean I should use DataSets to
keep track of the changes happening in the form? I shouldn't need any extra
fields in my form besides those which are part of the data fields, right?
What is the magic that the webservice has to do? Something along these lines?
http://msdn.microsoft.com/en-us/library/aa202748(office.11).aspx


2) I attempted to do this, using the guidance at this article:
http://msdn.microsoft.com/en-us/library/bb608324.aspx
However, I need repeating Rich Text boxes, I was unable to figure out how to
do the mapping between the non-data-field Rich Text box and the data-field
bound text boxes. The article describes keeping track of which Rich Text box
should be mapped to which text box using ID's, but I wasn't able to figure
out how to do this for new data, as there is no id generated until after
submission. I am also unclear on where the Rich Text field needs to be nested
on the form in order to work.

3) I also attempted this approach using the guidance at
http://aspalliance.com/1106_Saving_InfoPath_Forms_to_SQL_Server_2005_as_XML
However, while my schema for the XML column was the following:
<xsd:element name="RichTextBoxField">
<xsd:complexType mixed="true">
<xsd:sequence>
<xsd:any
minOccurs="0" maxOccurs="unbounded" namespace="http://www.w3.org/1999/xhtml"
processContents="lax"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>

However, when I create a web service for InfoPath to connect to, and examine
the InfoPath generated schema for the same field, I get the following:
<xs:element name="RichTextBoxField" minOccurs="0" maxOccurs="unbounded">
which unfortunately doesn't allow Rich text.

Any help or suggestions would be appreciated.

 

[Ben Walters]

Hey David,
I guess the first question here is how do you want to store the rich text
information?
for example does your business rule require that all formatting is stored
for later use?
if not then you could simply use a multi-line text box rather than a rich
text box to capture the required information.

If you do need to maintain the formatting then the next question that arises
is what you want to do with that information later? for example are you
planning on using that xhtml to populate another form or some other sort of
web interface? this could affect how the data is stored.

You also made mention to using datasets to track changes to the form, is
change control also a requirement of this solution?

If your using a web service then the easiest approach is to create a web
method that accepts an xml document as it's variable, then using the code
behind in infopath connect to this web serice and submit either the full
form xml or just the nodes in question using .net code. once the Xml has
been passed through to the web service you can then pass it through to SQL
to be stored.

If you plan to take the mapping approach to copy the rich text data accross
to another field all you need to do is put the text node in the same group
as your rich text node as InfoPath will work in context when the event is
fired.
for example if your xml looke like this

<root>
<Group1>
<RichText></RichText>
<Text><Text>
</Group1>
</root>

the Group1 node would be repeating when the change event fires on the
"RichText" node you could add a rule in the design engine that copies the
new data to the "Text" node this would be done in the context of the item
entered.

if you wanted to acheive this functionality using code you would then need
to cast the "Sender" object as an xpathnavigator object to access the node
that fired the event.

if you need more details on this let me know and I'll post some information
about it

Cheers,
Ben Walters

 

[Ben Walters]

Hey David,
I guess the first question here is how do you want to store the rich text
information?
for example does your business rule require that all formatting is stored
for later use?
if not then you could simply use a multi-line text box rather than a rich
text box to capture the required information.

If you do need to maintain the formatting then the next question that arises
is what you want to do with that information later? for example are you
planning on using that xhtml to populate another form or some other sort of
web interface? this could affect how the data is stored.

You also made mention to using datasets to track changes to the form, is
change control also a requirement of this solution?

If your using a web service then the easiest approach is to create a web
method that accepts an xml document as it's variable, then using the code
behind in infopath connect to this web serice and submit either the full
form xml or just the nodes in question using .net code. once the Xml has
been passed through to the web service you can then pass it through to SQL
to be stored.

If you plan to take the mapping approach to copy the rich text data accross
to another field all you need to do is put the text node in the same group
as your rich text node as InfoPath will work in context when the event is
fired.
for example if your xml looke like this

<root>
<Group1>
<RichText></RichText>
<Text><Text>
</Group1>
</root>

the Group1 node would be repeating when the change event fires on the
"RichText" node you could add a rule in the design engine that copies the
new data to the "Text" node this would be done in the context of the item
entered.

if you wanted to acheive this functionality using code you would then need
to cast the "Sender" object as an xpathnavigator object to access the node
that fired the event.

if you need more details on this let me know and I'll post some information
about it

Cheers,
Ben Walters

 

[David Fries]

Thanks for your prompt reply Ben.

First let me elaborate on my scenario a bit.
I will need to store Rich Text (not just multi-line text), and be able to
query it again from the DB at a later time.
I'm planning on having a separate view which aggregates multiple form
submissions together into one, and will export that view to .mht, but other
than that, no other plans to use the data in another form.

The reason I mentioned DataSets is because I do want to track changes.
If a user queries for a previously submitted form, edits some data, and then
submits it back, I want to track those changes.

Based on this quote from the InfoPath blog, I assumed that DataSets were
necessary if I was connecting to a SQL DB via a Web Service and I wanted to
track changes.
http://blogs.msdn.com/infopath/archive/2007/03/21/infopath-data-connections-part-1.aspx
"Web Service – Using a data connection bound to the Web Services adapter,
data is submitted according to the specifications of the web service.For web
services that expose the type System.Dataset, InfoPath will track changes as
the user edits the data. "



Also, thank you for your example on how to help out with mapping.
My data structure looks something like the following:
<myFields>
<queryFields>...</queryFields>
<dataFields>
<d:Header>
<RepeatingData>
<!-- WANT TO INSERT RICH TEXT BOX IN THIS CONTEXT-->
</RepeatingData>
</d:Header>
</dataFields>
<myInsertedFields>
<RichTextField/>
</myInsertedFields>
</myFields>

When I drag insert the Rich Text box, a Repeating Section bound to
myInsertedFields is also inserted, with the warning "control cannot repeat
here"
If I pull the Rich Text box outside of the repeating section, the warning
turns to an error: "control cannot store data"

I suppose the warning is acceptable, as the rich text box is still inside
the Repeating Data context, and can repeat that way.

However, as this mapping approach was mentioned in an article talking about
DataSets, I'm under the impression that I will need to use this mapping
approach if using DataSets.
If that's not the case, please let me know.

Sorry for the length of this post.

And finally, congrats on your new baby! (I checked out your blog)