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.)
[select1 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,"<","<")
strSQL=replace(strSql,">",">")
'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.
