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

,

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.

Rate

5 (2)

Share

Share

Rate

5 (2)