The overwhelming majority of ColdFusion applications on the Web and in our intranets are tightly integrated with a relational database management system of some sort. Because of its low cost, ease of installation and administration, and excellent performance, Microsoft SQL Server is one of the more widely used RDBMS platforms by ColdFusion developers. The recent release of SQL Server 2000 has added many new features to the SQL Server product line, most significantly a suite of features that leverage the power of XML.
Traditionally, ColdFusion developers had three methods of working with XML within their ColdFusion templates.
- Manually create or read XML packets by either building a string or parsing a file within a loop
- Employ an XML parsing construct, such as the Microsoft XMLDOM COM object or a COM, Java, or CORBA object
- Most common, leverage WDDX with the <CFWDDX> tag
These three methods of have always served ColdFusion developers fairly well, but they don't always scale well and may have other drawbacks, such as being verbose, resource-intensive, and/or too dependent on external technologies.
Finally, the common best practice of "letting the database work for you" can be extended to working with XML, as ColdFusion developers can leverage the power of their SQL Server databases when working with data in XML format.
The three new main features in SQL Server 2000 are:
- FOR XML: A Transact-SQL clause for extracting data as XML
- Virtual Mappings: Directly access your SQL Server Database via HTTP
- OpenXML: A T-SQL function for parsing through data formatted as XML
We will briefly examine how each of these new features works, and how they may affect ColdFusion applications that need to make use of XML representation of your database data. The examples in this article use a simple table ("States") made up of three columns ("StateID", "StateAbbreviation", and "StateName") and populated with 51 rows of data.
FOR XML
The FOR XML clause is used to return data from a SQL Server 2000 database in XML format by appending the FOR XML clause to a SQL SELECT statement. The syntax to retrieve data as XML is:
SELECT {columns}
FROM {table}FOR XML mode [,XMLDATA]
[,ELEMENTS] [BINARY base64]
The only required argument to FOR XML is its mode, which has three possible values:
- RAW: Returns data in XML format using default element names. Each query row is represented as a <ROW/> element, with table column names used as element tag attributes whose attribute value is the same as the column value. For example, the first row from my "States" table would be returned as <row StateID="1" StateAb-breviation="AL" StateName="Alabama"/>.
- AUTO: Returns data in XML format using table and column names to represent both tags and attributes. The optional FOR XML argument "ELEMENTS" is used in conjunction with a FOR XML AUTO clause to denote that data is to be returned as XML. This XML uses column values inside of opening and closing column name tags, surrounded by opening and closing table name tags, to denote each query row (<tablename><column name>value </column name></tablename>).
The preceding RAW example would look like this in FOR XML AUTO, ELEMENTS format:
<STATES><StateID>1</StateID><StateAbbrev
iation>AL</StateAbbreviation><StateName>
Alabama</StateName></STATES>
In AUTO format without the keyword ELEMENTS specified in a query, the XML generated uses the table name for each element name, with column names and their values represented as element attributes and their respective attribute value (<tablename columnname=value columnname=value/>). The same row shown in the two prior examples would be formatted like the following when FOR XML AUTO is used in the absence of the ELEMENTS argument:
<STATES StateID="1" StateAbbreviation="AL" StateName="Alabama"/>
- EXPLICIT: It's too complex an implementation to detail here; it allows for user-defined element names and element-nesting (mapping) definitions. Whenever possible, use one of the other two mode values, unless you have time to become comfortable with FOR XML EXPLICIT syntax.
In addition to the required mode parameter and the optional ELEMENTS keyword attribute, there are two more optional attributes when selecting FOR XML data; both (like ELEMENTS) can be specified only when mode is set to AUTO:
- XMLDATA: When this keyword argument is specified, the XML packet of data is returned, preceded by an XML data definition that describes the schema, element types, and attribute types. A snippet of the data definition from the above example would look like:
<AttributeType name="StateID"
dt:type="i4"/><AttributeType
name="StateAbbreviation"
dt:type="string"/><AttributeType
name="StateName" dt:type="string"/>
- BINARY Base64: The default attribute for AUTO mode SELECT statements; it specifies that the data should be returned in Binary Base64-encoded format. Base64 is a common content-encoding format for data sent via HTTP, which a browser will then decode and display in readable format.
If I wanted to extract all of the data from my States table as XML, I would type the following query, SELECT * FROM STATES FOR XML AUTO, into my SQL Server Query Analyzer window.20
And the (partial) result in Query Analyzer would look like Figure 1.
Note that all States table records are returned as XML in a single column query result. This makes the data less difficult to store, transfer, or syndicate, and more compatible with any application (CF or otherwise) that parses XML "straight from the source." Also note that the database assigns a UUID as the name for this column. UUIDs are not valid variable names in ColdFusion because of their use of special characters. Running the same query in a CF template will result in no errors and a record count of one, but trying to output, parse, or reference the UUID named XML packet will result in an error.
To run these FOR XML queries, we use another one of SQL Server 2000's new features - Virtual Mappings - to pass the SQL statement directly to the database via HTTP by appending it to a special URL or requesting an XML file resource.
Virtual Mappings
SQL Server 2000 closely integrates with Internet Information Server to allow for direct database access via HTTP. This means that the SQL statement can be executed by users and resulting query results displayed by making a URL request. This feature is implemented by creating virtual directories and virtual names. The first thing that must be done to configure a SQL Server 2000 database to allow HTTP access is to create a virtual mapping.
A SQL Server virtual directory is no different from a Web server virtual directory, except it points to a database on the database server rather than a directory on your hard drive. To create a virtual directory, launch the Configure SQL XML Support in the IIS utility found in the SQL Server folder of your Windows start menu, as shown in Figure 2.
"Configure SQL XML Support in IIS" is a Microsoft Management Console utility that allows developers and administrators to create virtual directories and virtual names. In addition to assigning a name (alias) to the new virtual directory, you may also specify a path to a local directory if you're going to access local files (see the virtual names section of this article for more details).
In addition to assigning an alias and pointing it to a directory, before accessing the virtual name you must point it at an accessible database server and database residing on that server, and then specify the Windows OS or SQL Server-level security settings the new virtual name should use when accessing the database.
It isn't necessary to change the default settings that allow or deny user operations when accessing the database via a virtual name (see Figure 3); not doing so should be thought of as the equivalent to creating a directory off of your Web root and not verifying user permissions in that directory. While the default settings favor strong security, not verifying them is an irresponsible practice. The permissions you can set are:
- Allow URL queries: Enabling this allows SQL statements to be passed to the database directly on the URL.
- Allow template queries: The only setting enabled by default. It allows XML templates containing SQL statements to be directly called by Web browsers.
- Allow XPath: Allows users to execute XPath queries against mapping schemas.
- Allow Post: Allows HTTP post operations to the virtual directory. This is necessary for operations, such as passing form variables into stored procedures.
Virtual Names
Virtual names are aliases similar to virtual directories, but they're used to allow access to a single file or to a directory of files. A virtual name can also be created to allow access to schemas or to dbobjects. When creating a virtual name in the virtual name tab of the Configure SQL XML Support in IIS utility, specify the type as either dbobject, schema, or template (if you're going to use the virtual name to access files).
Let's look at the differences between using the URL and using XML files to access your SQL Server 2000 database via HTTP.
To access a SQL Server database via the URL, point your browser to the machine's DNS or IP address (the virtual name), and then append your SQL statement. The SQL is appended as a URL variable called SQL, and by using a "+" to replace spaces in the SQL statement.
It's also important to note that the laws that govern well-formed XML dictate that there needs to be one parent element encapsulating child elements, so you must also tell SQL Server 2000 what name to use for the parent tag that will contain all of the child tags ("elements") that represent rows. This is done by also appending a root=rootname clause to the URL with an ampersand (rootname is whatever name you want to give to base the element).
A side note to this is that those of you who are familiar with XSL and want to create an XSL document to format and/or parse through the XML created by a SQL HTTP request passed to SQL Server 2000 can do so by passing the optional "XSL=xslfilename" attribute to the URL (where "xslfilename" equals the name of the XSL document they want to load). If I wanted to retrieve the same data result as before (an AUTO mode XML packet of all the rows in my States table) with a base element of CFDJStates, I would browse to http://127.0.0.1/cfdj_sqlserver2K?SQL=SELECT+*+FROM+STATES+FOR+XML+AUTO&root=CFDJStates.
The resulting page in Internet Explorer is shown in Figure 4.
There are two obvious problems with this method of data retrieval.
The first is that it's highly insecure, as the SQL statement is in plain sight of anyone who browses it, and can thus be manipulated by end users. This is a problem if you don't want your database schema to be known, want to restrict what data can and can't be retrieved, or would like to reduce temptation to users to call system SPs, delete data (or drop tables), or do any of the other nasty possibilities when you have direct access to pass SQL statements to a remote database.
The other reason you might not want to use this approach (as if security isn't enough) is the simple fact that you would probably like to either manipulate the data or present the data in another format to your end users.
The best workaround to this is to request the URL via the CFHTTP tag. If you request a URL with SQL appended to it via CFHTTP, specify "GET" for the value of the METHOD attribute in most scenarios. Requesting the XML packet in the URL as part of a CFHTTP request not only prevents exposing the SQL to users, but places it into a variable with a valid name so that we can work with it in our CFML templates by accessing #CFHTTP.File Content#.
The second method of HTTP retrieval of data as XML is via file. You can create an XML file that has SQL embedded inside it, create a virtual name that points to that file or its directory, and then request that virtual name (append the filename as if it were any other file embedded in any Web root subdirectory if your virtual name points to a directory) in order to execute your SQL. An XML file that executes the same SQL shown in previous examples would look like this:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT *
FROM States
FOR XML AUTO
</sql:query>
</ROOT>
If I were to save this text as a file called "getstatesdata.xml" in the same directory to which I pointed a virtual name called "xml_files", pointing my browser at http://127.0.0.1/cfdjsqlserver2K/xmlfiles/_getstatesdata.xml would yield the same results I received in the last HTTP request made on the URL directly, but the SQL details have now been obfuscated by simply requesting a file that contains the SQL I want to execute.
OPENXML
The last of the major new enhancements to the SQL Server product line with the release of SQL Server 2000 is the OPENXML command, which, in my opinion, is the most significant of the new features.
OPENXML is a command used within stored procedures to read in an XML packet and cache it internally, then operate on the XML data. Before calling OPENXML in a stored procedure, you must call the system stored procedure sp_xml_preparedocument to internally cache the XML packet before being used by OPENXML, and call sp_xml_removedocument after the call to release the internal cache of the XML packet. The OPENXML function accepts three attri-butes (see Listing 1).
In Listing 1, "@statedata" is the variable used to hold the XML packet, and @hDoc is the index variable used to identify the XML packet. Note that the variable is declared internally, passed to sp_xml_preparedocument, passed to the OPENXML statement, and finally passed to the sp_removedocument stored procedure.
In order to efficiently and securely copy all 51 rows from the "States" table to the "StatesArchive" table, a few steps should be performed.
First create a stored procedure to receive, index, and then insert an XML packet into another table. Once that stored procedure is created, create an XML file that calls the stored procedure in a virtual name directory. Last, create a CFML template that retrieves all of the data from the States table as an XML packet, and removes the first line of text ("<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">" is not part of a valid packet expected by the OPENXML function, as it's neither a parent nor a child element, but an XML declaration), and, last, passes the XML packet to the stored procedure we just created.
Let's assume the T-SQL example for OPENXML from above is saved as a stored procedure called copystates. The only thing left to do to retrieve the data in the States table and implement the OPENXML stored procedure is create and execute the template shown in Listing 2.
The average time to retrieve all of the data in the States table as XML, pass it to a stored procedure, and have the stored procedure insert all of the data from the XML packet into the StatesArchive table (51 rows - about 5K of data) was 10 milliseconds.
This is lightning fast compared to results using any other method - primarily because of SQL Server's internal caching of the XML, and the fact that my database traffic to pass the data to be inserted to the database is cut down to a single call. The benefits of using OPENXML for archiving are impressive. Coupled with the new HTTP accessibility features, ColdFusion developers also have some powerful new tools for B2B data syndication.
Where Do We Go from Here?
This article only touches on the new features available in SQL Server 2000. If your company or client has already made the investment in SQL Server 2000, I hope this article has served as enough of a primer to get you started.
If you're not yet working with SQL Server 2000, you should now feel fairly comfortable in any situation where you might be asked whether or not upgrading or investing in a SQL Server 2000 database server offers any benefits to your company or client. Or you may even be asked how a project plan or application code base might be approached differently were SQL Server 2000 used as the database platform instead of some other database.
The potential uses for SQL Server 2000's new XML-based features are limited only by your application needs and your imagination. For example, XSL offers developers a way to format the layout and appearance of XML in a browser, and has its own simple, conditional logic and looping constructs. This could be leveraged along with the features discussed in this article to run queries, loop over that query result set, and manipulate the display for users - all without burdening your CFAS with any CFOUTPUT, CFQUERY, or CFIF calls.
Letting the client's processor execute loops of this sort can make a significant impact on site performance as well as enhance user experience. There are other technologies like WDDX, SOAP, and Microsoft's .NET technology, and Java (to name a few) that are tightly integrated with or based on XML and feature rich XML parsing abilities. SQL Server's new XML features will allow developers to leverage these technologies and integrate them with their data environments much easier.
If you want to learn more about the new XML features in SQL Server 2000, I recommend reading the recently released Professional SQL Server 2000 XML by Paul J. Burke, et al (Wrox Press). It covers these topics and many, many more in much greater detail and is an excellent resource for those wishing to delve into SQL Server's new XML features.
Subscribe to ColdFusion Developer's Journal Email News Alerts
Subscribe via RSS
