Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

ASP - Reaping the benefits of SQL 2000's FOR XML clause.

By Leon Platt,

ASP - Reaping the benefits of SQL 2000's FOR XML clause.

In the several years that I have been programming ASP pages, I have seen countless methods of dynamically filling HTML drop down list boxes. I myself have developed several re-usable routines for doing this. Being the XML guru that I am, I decided recently to play around with some of sql2000's XML features. It wasn't too long before it hit me.... I wonder if I could get SQL server to return a completely populated HTML list box with a simple query? After all, an HTML Select Tag is nothing more than an XML document! The syntax looks like this:

<SELECT>
<OPTION value="return Value">display Value</OPTION>
<OPTION value="return Value">display Value</OPTION>
</SELECT>

What if I could get SQL2000 to return the results of my query in XML that looks like the above or at least something very similar. That is what I set out to accomplish. The first thing I realized was that I could hard code the actual <SELECT> tag and just concentrate on getting sql to return the <OPTION> tags. If I could get sql2000 to return something like this I'd be set:

<OPTION value="return Value">display Value</OPTION>
<OPTION value="return Value">display Value</OPTION>

The second thing I realized was that I wanted sql2000 to return back a combination of Elements and Attributes. I wanted my first column (the return value) to be an attribute but, I wanted my second column (the display text) to be returned as text data of the Element. The only way to do this is by using the FOR XML EXPLICIT option. Before I talk about the complexities of FOR XML EXPLICIT, I would like to go over the simpler FOR XML RAW and FOR XML AUTO clauses and explain how they got me almost there but not quite. The example I would like to use for this article is filling a drop down list box with the names of all the databases on a specified sql server. The sql query looks like this: "Select dbid,name from sysdatabases order by name" and returns results like this on my sql server:

[Select  dbid,name from sysdatabases order by name]
dbid   name                           
-------------------------------------------------------------------
1      master
3      model
4      msdb
6      Northwind
7      Plastics
5      pubs
2      tempdb

Exploring the FOR XML RAW clause

Now lets take the query and add "FOR XML RAW" to the end of it. The results are shown bellow: Note that the elements are assigned the generic name "row" and the columns are returned as attributes. Not exactly what I'm after... Lets look at the next option.

[Select  dbid,name from sysdatabases order by name FOR XML RAW]
-----------------------------------------------------------------------------------------------------
<row dbid="1" name="master"/>
<row dbid="3" name="model"/>
<row dbid="4" name="msdb"/>
<row dbid="6" name="Northwind"/>
<row dbid="7" name="Plastics"/>
<row dbid="5" name="pubs"/>
<row dbid="2" name="tempdb"/>

Exploring the FOR XML AUTO clause

Now lets take the query and add "FOR XML AUTO" to the end of it. With this option I could actually alias the table as "option" and alias the column dbid as "value" and get closer to what I wanted. The results are shown bellow: Note that all the columns are still returned as attributes. The question is how do I get the column "name" to be returned as an Element value?

[Select  dbid,name from sysdatabases as [option] order by name FOR XML AUTO]
-----------------------------------------------------------------------------------------------------
<option value="1" name="master"/>
<option value="3" name="model"/>
<option value="4" name="msdb"/>
<option value="6" name="Northwind"/>
<option value="7" name="Plastics"/>
<option value="5" name="pubs"/>
<option value="2" name="tempdb"/>

Exploring the FOR XML EXPLICIT clause

The answer of course is using the "FOR XML EXPLICIT" clause. Now lets take the query and add "FOR XML EXPLICIT" to the end of it. The results are shown bellow: As you can see this isn't going to be that simple.

[Select  dbid,name from sysdatabases as [option] order by name FOR XML EXPLICIT]
-----------------------------------------------------------------------------------------------------
Server: Msg 6801, Level 16, State 1, Line 1
FOR XML EXPLICIT requires at least three columns, including the tag column, the parent column, and at least one data column.z

"FOR XML EXPLICIT" requires that you format your query in a manner that will return a hierarchy of data. The actual syntax can be very confusing because you need to try to place each selected column into its correct location within the hierarchy. Also you are given the flexibility to make the data item an attribute or an element. Typically the explicit option is used when you are trying to relate two or more tables together such as Orders and Order Details. However, with some manipulation we can produce our desired results fairly simply: (I say simply now but it actually took several hours of messing with the sql syntax before I actually began understanding it.)

[	select  
	1 as tag,				'This line always stays the same
	Null as parent,  			'This line always stays the same
	dbid as [option!1!value],  		'Replace dbid with your value column name
	name as [option!1!]  			'Replace name with your display column name
	from sysdatabases 			'Replace sysdatabases with your table name
	order by name FOR XML EXPLICIT	]	'Order by whatever you want
-----------------------------------------------------------------------------------------------------
<option value="1">master</option>
<option value="3">model</option>
<option value="4">msdb</option>
<option value="6">Northwind</option>
<option value="7">Plastics</option>
<option value="5">pubs</option>
<option value="2">tempdb</option>

SQL Server; Fill another list box please!

I think this is pretty COOL! I can run queries that return drop down list boxes! To make this concept more versatile I have developed a few VB Script functions that can be included in your ASP pages. Here is the sample ASP page that fills a drop down list box with the names of all databases on my sql server:

<%@ Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<H2>SYSTEM  DATABASES</H2v
<SELECT>
<%
set dbConn=server.createobject("adodb.connection")
dbConn.Open "file Name=c:\code\cnLanWeb.udl"
strSQL="select  dbid,name from sysdatabases order by name "
response.write buildOptionsFromSql(dbConn,strSQL)
%>
</SELECT>

</BODY>
</HTML>
<!--#include virtual="/testing/_PRIVATE/vbFn_buildOptionsFromSQL.INC"-->
<!--#include virtual="/testing/_PRIVATE/vbFn_runSqlGetXML.INC"-->
<!--#include virtual="/testing/_PRIVATE/vbFn_stripXMLroot.INC"-->

vbFn_buildOptionsFromSQL.INC

To use this function just pass in a reference to an ado connection and a standard sql select statement. This function will automatically take your sql statement and reformat it into a new sql statement using the FOR XML EXPLICIT clause. The results returned will be all of the option tags for your drop down list box. Note: this function has not been tested against every possible sql statement and may require modification to handle statements with complex syntax. Note: this function is dependent on two other functions: (1)runSQLgetXML and (2)stripXMLroot. See bellow:

<%
'This function accepts a reference to an ado connection
'	and a sql statement to execute
'This function returns the HTML used to add the options to a SELECT TAG
function buildOptionsFromSql(dbConnection,sql)
	'response.write sql
	'
	'--Need to convert the standard sql statement into a new sql statement
	'	using for XML that will return the items in the format for a SELECT TAG
	'   ie each item will be returned as <OPTION value="field1">field2</OPTION>
	'the sql should contain one or two fields
	'
	' first thing we do try to find the column Names in the sql statement
	start=1
	pos=instr(start,sql,"SELECT",1) 'Find the position of SELECT
	if pos=0 then
		buildOptionsFromSQL="<OPTION value=""-1"">Bad SQL</OPTION>"
		exit function
	else
		start=pos+6
		pos=instr(start,sql,"DISTINCT",1) 'does the sql contain DISTINCT
		if pos>0 then
			start=pos+8
		end if
		pos=instr(start,sql,"TOP",1) 'does the sql contain a TOP clause
		if pos>0 then 
			start=pos+3
			pos=instr(start,sql,"PERCENT",1) 'dose the sql contain a PERCENT clause
			if pos>0 then
				start=pos+7
			end if
		end if
		sqlSelect= left(sql,start-1) 'remove the select part of the sql statement and save it in sqlSelect
		sql=right(sql,len(sql)-start)
	end if
	pos=instr(1,sql,"FROM",1) 'find the FROM clause and we know that anything to the left is our column Names
	if pos=0 then
		buildOptionsFromSQL="<OPTION value=""-1"">Bad SQL</OPTION>"
		exit function
	else
		fields=left(sql,pos-1) 'remove the column names and store them in the variable fields
		sql=right(sql,len(sql)-(pos-1))
	end if
	pos1=instr(1,fields,",")
	if pos1=0 then 'only one field displayed value and return value will be the same
		field1=fields 'field1 is the returned value from the dropdown
		field2=fields 'field2 is the value displayed in the dropdown
	else
		pos2=instr(1,fields,")")
		if pos1<pos2 then
			pos1=instr(pos2,fields,",")
			if pos1=0 then 'only one field displayed value and return value will be the same
				field1=fields 'field1 is the returned value from the dropdown
				field2=fields 'field2 is the value displayed in the dropdown
			else 'returned value and displayed values are different
				field1=left(fields,pos1-1)
				field2=right(fields,len(fields)-(pos1))
			end if
		else 'returned value and displayed values are different
			field1=left(fields,pos1-1)
			field2=right(fields,len(fields)-(pos1))
		end if
	end if
	pos=instr(1,field1,"AS",1) 'handle situation where column 1 is aliased
	if pos>0 then
		field1=left(field1,pos-1)
	end if
	pos=instr(1,field2,"AS",1) 'handle situation where column 2 is aliased
	if pos>0 then
		field2=left(field2,pos-1)
	end if
	're-assemble the query using the parts parsed out and inserting 
	'	valid syntax using the for xml explicit clause
	strSQL=sqlSelect & " " & _
	"1 as tag, " & _
	"Null as parent, " & _
	Field1 & " as [option!1!value], " & _
	Field2 & " as [option!1!] " & _
	sql & " for xml explicit " 	
	'response.write strSQl
	'response.end
	'
	'strSQL is now valid sql that will return xml results in the format we want
	'call the routines execute the sql (runSQLgetXML)  results are returned in xml
	'we do not want the root xml tag all we want is the <OPTION> tags
	'	so we call stripXMLroot
	buildOptionsFromSql=stripXMLroot(runSQLgetXML(dbConnection,strSQL))
end function
%>

vbFn_runSqlGetXML.INC

To use this function pass a reference to an ADO connection and pass in a Sql Statement that uses one of the FOR XML clauses. The results of the function will be a string of XML data. This is a very useful function for other situations besides filling list boxes. Typically, in order to produce an XML string you would query sql, open a recordset, loop through the recordset and build a XML string based on the data. All of this work can be avoided using this function.
<%
function runSQLgetXML(dbConnection,strSQL)
	'dont want greater than and less than symbols in sql to be interpreted as xml tags
	strSQL=replace(strSql,"<","&lt;")
	strSQL=replace(strSql,">","&gt;")
	'response.write strSQL
	set objCmd = Server.createObject("ADODB.Command")
	set xml = Server.createObject("ADODB.Stream")
	xml.Open		'results of ado command will be sent to a stream object, open the stream to get ready
	objCmd.ActiveConnection=dbConnection
	'format sql command in xml
	objCmd.CommandText="<root xmlns:sql " & _
		"='urn:schemas-microsoft-com:xml-sql '>" & _
		"<sql:query>" & _
		strSQL & _	'insert sql command into xml packet
		"</sql:query></root>"
	objCmd.Dialect="{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"	'let sql know command is in xml format
	objcmd.Properties("Output Stream").Value=xml 		'set command object to output to a stream
	objCmd.Execute ,,1024	'run the sql command
	set objCmd=nothing
	strOut=xml.ReadText	'get the results from the stream object and save in a variable
	xml.Close
	set xml = nothing
	runSQLgetXML= strOut	'return the results as a string
end function
%>

vbFn_stripXMLroot.INC

To use this function pass in a XML string that was produced by running a query on sql server with one of the FOR XML clauses and the function will strip off the root element. Note: this function would be more flexible if an XML dom was instantiated and the root element was dropped via a method of the dom. This function is a light weight and just uses the vb script replace method to strip the root element from the string. The function assumes that the root element will consist of the specific data hard coded bellow. If Microsoft changes the data the function will break. Another possible light weight solution would be to use regular expressions to strip off the root element. The reason we are stripping off the root element in this example is because we are only interested in the <option> tags.
<%
function stripXMLroot(strXML)
strOut=replace(strXML,"<root xmlns:sql=""urn:schemas-microsoft-com:xml-sql "">","",1,1)
strOut=replace(strOut,"</root>","",1,1)
stripXMLroot=strOut
end function
%>

CONCLUSION

I couldn't begin to count the number of times I have written ASP code to populate a recordset and then found myself looping through it formatting the data into HTML, XML or some other structure. SQL server 2000's FOR XML EXPLICIT clause opens up many possibilities to ASP developers who have themselves been doing the same as I have been for years. Today it is possible using the techniques I've presented here to retrieve almost any data with structure already preformatted and ready to use. How about writing "for xml" queries to return HTML tables. Even better, I have used these techniques to return the data as XML, and then send the XML through a XSL style sheet to produce output in just about any format imaginable. This is very cool and I will demonstrate this in a future article on XSLT. The key here is that we can get XML data from sql server 2000 that is ready to use! Why format the data ourselves when sql server 2000 will do it for us and faster.
Total article views: 8078 | Views in the last 30 days: 5
 
Related Articles
FORUM

user defined functions & columns

user defined functions & columns

FORUM

Function with Select * that references a View

Select * in the Function does not reference new columns added to a View

FORUM

Help Needed - Function Returning Table

Function Returning Table

FORUM

Return result of dynamic query from function

Return result of dynamic query from function

FORUM

Select statement of view stays 5 hours to return results......

Select statement of view stays 5 hours to return results......

Tags
ado    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones