Introduction.
Hi friends, here I've included the code to extract XML data from a SQL Server 2000 database using dynamically generated XML templates. The prerequisites include working knowledge of XML, familiarity with SQL Server Joins and of course VB.NET and webservices. To try out this example you must have SQL Server 2000 or SQL Server 7.0 with XML support. This webservice is part of a project called the CWS(Catalog Web Services) and offers result data services based on the input parameters. The input parameters come from another webservice called as the MetaData Service(which has been dealt with in an earlier article).
This webservice has a public webmethod, getData which accepts username, password, catid, keywords and displayfields as parameters. The username and password will be used by the web service for authenticating the user before carrying on the next process. After authentication the remaining of the parameters are passed to the genXMLtmpl method which generates the XML template which SQL Server will utilize later on.
In genXMLtmpl, the keywords parameter is checked for characters and if found NULL, the webservice assumes that the user wants to query based on the category Id. The displayfields is the parameter which contains a string with fieldnames separated by comma.
Now, the catid or the keywords is sent to overloaded functions genQryClause (ByVal catid As Integer) As String or genQryClause (ByVal keywords As String) As String which will generate the query clause to be used by the XML template.
The later part of the genXMLtmpl function will generate the XML template using the XML explicit keyword,joins and clause based on the genQryClause output and return the final SQL Server XML template to getData. This XML template is passed to the ADO connection object where it is executed and the result is returned in XML.
For comma separation I have included a legacy component(which .NET has wrapped with its own TLB). For the authentication class, refer to my prefer article. The source code and the database scripts are included in the ZIP along with this article.