Foam is a .Net Windows application that is used to ease the creation of FOR XML
EXPLICIT queries for use with MS SQL Server 2000. Foam does this by taking an
XML document that you have formatted according to the incredibly simple
structure specified below, and then parsing through the document to create the
appropriate FOR XML EXPLICIT structure. This is the absolute base functionality
of Foam.
With Foam, you can further specify parameters, constraints(FROM/WHERE clauses),
and ORDER BY criteria. Foam will take the information that it has been given,
and format it, and place it in the appropriate location for the stored
procedure.
While Foam cannot hope to cover all the snags of working with FOR XML EXPLICIT,
it does have some rules built in to create the procedure structure as
accurately as possible to minimize errors. The more information you provide
Foam with, the less chance of errors and need for debugging there will be at
the end.
Foam requires that you have the Microsoft.Net Framework installed, and a resolution display of at least 800x600.
Formatting an XML file for Foam is so simple a monkey could do it(well maybe if you had an infinite number of monkeys...)! To format an XML document for use with Foam, create the XML structure that you would like for your end output. Instead of placing actual data values in for attributes and elements, just place in the column, string, expression, etc... that you would like to have SQL Server use as the value. Look at the Sample.xml file that came with the Foam.zip file for an example of how to format your document.
First you will need to either type in or use the Browse button to locate the
path to the XML document you wish to parse. After you have chosen the file you
want to parse, you can specify the name you would like to use for the stored
procedure.
In the Parameters section you can add, edit, and remove parameters for the
stored procedure. To add a parameter, specify a name, data type, and a length
if necessary(no parentheses). Click Add / Edit when you are done. To edit a
parameter click on that parameter's line in the parameters list. You may then
change the data type, or length and click Add / Edit to save the changes. To
remove, select the parameter you wish to remove from the list and click Remove.
The above are the sections you can fill out before parsing the document you
have selected. To make the ORDER BY, and Constraint Clauses groups available,
you will need to click the Parse XML button. If the parsing of your document is
successful, you will receive a message saying that parsing was successful. If
the parsing failed, you will receive a failure message. A failure message
indicates that the way you have formatted your document is not in line with
what Foam was expecting. Please compare your document with Sample.xml as a
reference.
To add ORDER BY criteria to the procedure, select the path to the tag you wish
to order by from the drop down, specify whether it should be ascending or
descending, and click Add. To edit, or remove, follow the same pattern as
described in the Parameters use information. You can move ordered fields up or
down in the list to get the appropriate location for a field even if it was
added at a later time. To do this, click on the field you want to change, and
use the up or down arrow buttons to move the field. It is recommended that you
add ORDER BY criteria for your procedure, this is because of the necessity to
have the column being ordered by SELECTed in all UNIONs. For more information
on how FOR XML EXPLICIT and ORDERing work you can read this article:
http://www.sqlxml.org/faqs.aspx?faq=28
To add Constraint Clauses(FROM/WHERE) choose the SELECT block from the drop
down that the constraint will be attached to, add your constraints in the
textbox, and click Add. To edit or remove a constraint, follow the pattern
described in the Parameters use information.
When you are satisfied with the data you have entered, you can click on the
Build Procedure button. This will popup a new window from which you can copy or
save the generated stored procedure. You always have the option of clicking on
the Build Procedure button as long as you have specified the XML document you
wish to parse.
Foam was developed by digital nothing design, because creating FOR XML EXPLICIT queries from the ground up is a major pain in the rear! Anyone who has attempted such knows that this is fact, and not even remotely close to opinion.
Below is a list of resources that you may find helpful while attempting to create FOR XML EXPLICIT stored procedures, or even your normal day to day SQL questions.
Now that you are finished reading this little bit of documentation, go have some Foam with your SOAP! ...I'll stop now. Have fun using Foam, hopefully you'll find it as useful as I have.