XML and the SQL 2000 Server, Part 2 - Stored procedures with templates
(Page 6 of 7 )
The following stored procedure takes an input variable @State and returns a number of columns from the authors table in the pubs database:
Create procedure MySimpleXML2
@State char(2)
as
Select stor_name, city, zip, state
from stores
where state=@State
order by city
for XML auto
Now a template file (StoredProc.xml) to query the database with this stored procedure is fashioned as follows:
The result from this stored procedure is obtained by executing the procedure, which is accomplished by sending it to the URL as follows:
http://XPHTEK/PubsVirtual/Templates/StoredProc.xml?State=CA for authors from California
and
http://XPHTEK/PubsVirtual/Templates/StoredProc.xml?State=WA for authors from Washington.
The results are as shown here for both cases:
Display XML results with style sheets In the generic format of the template files seen so far, the style sheet reference sql:xsl='xsl File Name' has not been used. If we reference a xsl style sheet, it will be possible to see the node tree. This can be displayed as an HTML document by further indicating that the content type is HTML.
Style sheet document structure The specific case of displaying the results of the stored procedure, which shows the results of the Stored Procedure MySimpleXML2 considered earlier, will be formatted in HTML using a style sheet, CAtest.xsl. Using the generic template shown earlier, the template is modified to include the style sheet for formatting as follows:
Compare this with the one without reference to the style sheet information. The additional information needed is the xsl file, CATest.xsl. This style she/A>. This earlier output can be processed by the following xsl file.
This file should be located in the same directory as the StoreProcStyled.xml file, namely the Templates directory. If this file is accessed by its URL, namely,
http://xphtek/PubsVirtual/Templates/StoredProcStyled.xml?State=CA the output will be the node structure, as follows:
Next: Display the HTML file >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
