This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

SQL Server 2000: New XML Features Streamline Web-centric App Development

icrosoft® SQL Serverâ„¢ 2000, formerly codenamed Shiloh, is slated to include a number of new features that are designed for the power database user. One of the most interesting is its XML capability. Three features in particular combine to make SQL Server 2000 an XML-enabled database server: You can access SQL Server through HTTP by building a SQL-compliant URL The SELECT statement has a new FOR XML clause that supports the retrieval of results in XML format The data in a database can be updated through an XML-based gram The inclusion of these three features will make SQL Server fabulously simple to use when building a Web site. In this article, I'll discuss these features, provide a demo application that puts them to use, and give you some tips on this upcoming version that will make your life easier.       Even though SQL Server 2000 is not yet a released product, you can get an early preview of its features by downloading and installing the XML SQL Technology Preview from MSDNâ„¢ Online (https://msdn.microsoft.com/downloads/samples/internet/default.asp?url=/Downloads/samples/Internet/xml/sqlxml/Default.asp). At press time, this preview was tested on Windows NT® 4.0 with Internet Information Server (IIS) 4.0 and Windows® 2000 with IIS 5.0, running SQL Server 7.0 or SQL Server 6.5 SP5. It does not currently work with Windows 98 and Personal Web Server. Getting Started       To start using the XML SQL Technology Preview, you should download it and follow the installation instructions. Make sure the machine is running IIS 4.0 or higher; the preview runs as an ISAPI-based DLL.       Once it's installed, you can start using the package by registering individual directories as virtual roots for a particular database. The download includes an MMC snap-in that lets you set up vroots the same way as you would with IIS. When you create a vroot with this snap-in, you can then double-click the new root to get a Properties dialog. This dialog lets you indicate general options (like the virtual directory's name and local path) as well as security options (SQL logins, Windows Integrated Authentication, or Basic Authentication), the SQL data source, and several settings (like whether you can use URL queries on this vroot).       For instance, if you have a database within SQL Server named tiger, you can create a virtual root named tiger that connects to the tiger database (see Figure 1). Be careful when you do this, howeverâ€"if a directory is set up as a virtual root for this SQL Server XML Technology Preview, it can only handle SQL-based queries and can't serve as a regular virtual root.       That's it! You're all set to use the SQL Server Preview. To make sure it worked, set up a vroot for an existing database, like Northwind. You're ready for your first XML-based SQL command. If you've set up the Northwind database with a vroot of Northwind, you can navigate to a URL like this:

https://localhost/Northwind?
sql=SELECT+firstname,lastname+
FROM+employees+FOR+XML+AUTO

All you need to do is pass the root an argument named sql, attaching a SQL statement that's valid for the database associated with the root. As with any URL, you should replace spaces with + signs. Any other questionable characters (like ', ', or &) can be replaced with escape sequences. When you issue the URL listed previously, you should get back some XML-based data in your browser:

<?xml version="1.0" encoding="UTF-8" ?>
<root>
<row firstname="Nancy"
lastname="Davolio" />
<row firstname="Andrew"
lastname="Fuller" />
<row firstname="Janet"
lastname="Leverling" />
<row firstname="Margaret"
lastname="Peacock" />
<row firstname="Steven"
lastname="Buchanan" />
<row firstname="Michael"
lastname="Suyama" />
<row firstname="Robert" lastname="King" />
<row firstname="Laura" lastname="Callahan" />
<row firstname="Anne" lastname="Dodsworth" />
</root>

It looks like an XML file. It smells like an XML file. But there's no source file! All the data is generated on the fly by SQL Server before the ISAPI module returns it in XML format.
      The big problem is that as handy as they are, all Northwind and Pubs samples tend to swirl together after a while. So I decided to go a different route by creating my own database. The U.S. Census Bureau has compiled a digitized street map called Tiger/LINE that provides street-by-street coordinate data for every county in the United States. This massive database is available for purchase by anyone. The data itself is free for distribution in any form, making it ideal for a demo like my own.
      Several years ago, I bought some of the files on CD-ROM, but with my then-primitive tools (a 286! Bah!) doing anything with the data was difficult and time-consuming. After a brief period of trying to import thousands of records into Microsoft Access and get them out again to draw lines with Visual Basic® primitives, I gave up. I went back to school, had kids, and got on with my life.
      As it turns out, this data makes a great sample database for an XML project like the one I'll show you. In just a couple of days, I used the features of the SQL Server XML Technology Preview to develop a sample that lets you display a rudimentary street map in a browser. There are also a bunch of other things you can do with this data if you want to experiment. I won't go into the full details of the data dictionary for the files I've used here, but you can peruse them at https://www.census.gov/geo/www/tiger/index.html . There are actually up to 17 files for most counties, although some are more important than others. Figure 2 shows the data dictionary for this record type. This is the type I'll be using most often in this article.
      The first challenge was to import the Tiger data into a new SQL Server database, which I did in multiple steps. First, I created a database named tiger and added two tables: States and Counties. The Tiger/LINE files are based on unique codes for each state and county, helping users to quickly identify data files. For instance, if you're looking for Suffolk County, New York, you can look in these tables to find that New York has a state code of 36, and Suffolk County has a county code of 103. I was able to import these tables through a bit of simple parsing because they only contain two or three columns. However, with over a dozen raw data files for each entity and a lengthy data dictionary for each, it can take a lot more effort to import individual counties. I used the SQL Server bulk copy utility, which I'll describe later.
      Based on the naming scheme of state/county, the data file containing the street information for Suffolk County is named tgr36103.f41. The f41 extension indicates the type of file: F4 means the file is from the 1994 version of the files, and 1 means that it's record type 1, or Complete Chain Basic Data Record. This will be important when performing a bulk import.
      As I mentioned previously, there are up to 17 files for each county; these include files with information like street addresses by block, Congressional district boundaries, and so on. For the sake of completeness (and if you want to play around with the data later), I'll import all the data for each county at once. The source code package for this article includes the files for Suffolk County. Importing this data is a two-step process: first creating the new database tables, and then importing all the data.
      To set up the tables, I created an easily modified script based on the data dictionary of the files (see Figure 3). Here, you need only change the variable @tb to the proper county code you'll be using. Load the file into SQL Server Query Analyzer and run it. Within seconds you'll have all your tables defined. Note that it doesn't drop the tables before creating them, so if you run it twice you won't accidentally kill your data.
      The next and slightly trickier part is importing the data itself. I found that the easiest way to do this was with the bulk copy utility, named bcp, that's included with SQL Server 7.0. bcp is a command-line tool that takes a data file and a format file as arguments, and blops all the data into the database (assuming the format works properly). Figure 4 shows the format instructions for a Type 1 file. The structure of this file is loosely defined since it uses any white space as a delimiter. You give it the version of the database (use 6.0 for backward compatibility), the number of fields in the import (in this case, 44), and one line for each input field. These lines consist of the input field number, the data type, the prefix length, the length of the field, the delimiter ("" for column-delimited data, "\r\n" for an end-of-line), the server column order, and the server field name, respectively.
      In addition to the source data file and a format file, bcp also takes the database table, the database server name, and the database user as arguments. To import the map data into a table on the server cordelia, run the command line

bcp tiger..TGR3606101 in
c:\tiger\tgr36061.f61
-fc:\tiger\f41.fmt -Scordelia -Usa

and all the data is magically imported for you. If it works properly, you'll get status messages every 1000 lines.

XML-format Calls

      Now that the database contains some nice sample tables, let's look at how SQL Server will handle XML-based datasets. First of all, note that XML statements are currently only available through a URL via HTTP. Take a relatively simple SELECT statement:

SELECT tlid,fedirs,fename,fetype,zipl,zipr
FROM tgr3610301 WHERE zipl != zipr

This selects a street or feature's unique identifier, name, and ZIP code when the ZIP code is different on the left and right of the feature. (In other words, any feature that's a ZIP code boundary.) If you run this in SQL Server Query Analyzer, you get standard row-based output (see Figure 5).
      You can retrieve this data from a browser in XML format. First, replace all spaces and other reserved characters with the appropriate substitute or escape character, and indicate that you want XML output with a FOR XML RAW specifier:

SELECT+tlid,fedirs,fename,fetype,zipl,zipr+
FROM+tgr3610301+
WHERE+zipl+!=+zipr+FOR+XML+RAW

Next, navigate to the path you've set up as a vroot for this database, using the string just mentioned as the sql argument to create a URL like this:

https://cordelia/tiger?sql=
SELECT+tlid,fedirs,fename,fetype,zipl,zipr+
FROM+tgr3610301+WHERE+zipl+!=+zipr+FOR+XML+RAW

If all is well, you should get the data in Figure 6 back in the browser in XML format.
      There are three XML formats you can use to retrieve your data: XML RAW, XML AUTO, and XML EXPLICIT. If you don't specify any XML type in a URL-based SELECT statement, one of two things can happen. If you select multiple fields, the call will fail. If you select a single field, you'll get a plain text representation of the value of the first record returned by the statement. The FOR XML RAW statement returns data like that shown in Figure 6. The row attribute is the default for all data in raw mode.
      If you want additional information in your XML output, you can specify auto mode. This will use the database table name as an attribute instead of the generic row attribute. In this case, navigating to the URL

https://cordelia/tiger?sql=
SELECT+tlid,fename,fetype,zipl,zipr+
FROM+tgr3610301+WHERE+zipl+!=+zipr+FOR+XML+AUTO

produces output like that shown in Figure 7.
      The third option, XML EXPLICIT, lets you specify exactly how the XML tree looks when it's returned. It's significantly more complex than the first two options.
      With FOR XML EXPLICIT, you can shape the way you want the XML tree returned to you. However, you also have to write queries in a specific format that clearly defines the output you want. Since these queries can become quite complex, it's best to use them in a template file, not within a URL.
      A query is built by generating a universal table. A universal table contains all the output data you want, plus two additional columns: Tag and Parent. If the Parent tag is NULL, the XML is stored directly under the root element. For each column you request, you must also note its tag number, output attribute name, and an optional directive. The directive can specify how the data is returned in the XML tree, or how it's bracketed (if it contains non-standard characters, for instance). A query that looks like this

SELECT 1 as Tag, NULL as Parent,
tgr3610301.tlid as [segment!1!tlid],
tgr3610301.fename as [segment!1!name!element],
tgr3610301.fetype as [segment!1!type],
tgr3610301.exel as [segment!1!lzip],
tgr3610301.zipr as [segment!1!rzip]
FROM tgr3610301 WHERE zipl != zipr
FOR XML EXPLICIT;

���
<segment tlid="113953250" type="Road" lzip="11939"
rzip="0">
<name>Rocky Point</name>
</item>
<segment tlid="113953251" type="Ave" lzip="11939"
rzip="0">
<name>Aquaview</name>
</item>
���

      The output columns are named in the SELECT statement, and the fename column is additionally marked to be an element of the item tag, rather than an attribute (the default). The tags themselves are named segment, the output column specified for these fields in the SQL statement.
      You can also get a data description of the XML output in either DTD or XMLData formats by appending your choice to the URL. For example, the URL

https://cordelia/tiger?sql=SELECT+tlid,fedirs,fename,
fetype,=zipl,zipr+FROM+tgr3610301+
WHERE+zipl+!=+zipr+FOR+XML+AUTO,DTD

produces the DTD at the top of the returned data, as shown in Figure 8. Similarly, you can request this information in XMLData format. The URL

https://cordelia tiger?sql=
SELECT+tlid,fedirs,fename,fetype,
zipl,zipr+FROM+tgr3610301+WHERE+
zipl+!=+zipr+FOR+XML+AUTO,XMLData

https://cordelia/tiger?sql=
SELECT+ tlid,fedirs,fename,fetype,zipl,zipr+
FROM+tgr3610301+WHERE+zipl=11786+
OR+zipr=11786

https://cordelia/tiger?sql=
SELECT+tlid, fedirs,fename,fetype,zipl,zipr+
FROM+tgr3610301+WHERE+zipl=11786+
OR+zipr=11786+FOR+XML+
AUTO&xsl=https://cordelia/XSLFiles/tigertable.xsl

The output of the original URL call is shown in Figure 11; the XSL-processed call is shown in Figure 12.       Figure 11Original URL Call Output       Figure 12XSL-processed Output Using Templates       You can now pack a SQL query into a URL if you set a vroot to allow it. That's useful for lots of applications, but you obviously have security considerations when trying this with a real-world app. Who wants to give a user access to the name of the fields in your database tables? You probably don't want them doing their own queries on your time. There's also the issue of complex queries. What if you want to generate XML data for multiple queries at once? Or use input parameters? Or even build complex queries with local variables?       When you set up the SQL Server XML Preview, you indicated a virtual root for a particular database connection. This vroot can be used for SQL queries, but the physical directory can also be used to hold SQL query template files. Unlike a standard vroot in IIS, this directory can't hold ASP or HTML filesâ€"it can only hold XML templates. A template is a simple XML file that includes a SQL query that will be executed against the database that's related to the directory you set up earlier.       Take a simple example from the tiger database. Suppose you want to select all the counties in New York State (state 36). If you put this query in a URL, it would look like this:

https://cordelia/tiger?sql=SELECT+*+FROM+counties+
WHERE+ state=36+FOR+XML+AUTO

<root>
<sql:query xmlns:sql="urn:schemas-microsoft-com:
xml-sql">
SELECT * FROM counties WHERE state=36 FOR XML AUTO
</sql:query>
</root>

The contents of this query are contained within a <root> element, which is the root node for this XML. The <sql:query> tag is based on a namespace defined in that tag itself ("urn:schemas-microsoft-com:xml-sql"). In the <sql:query> tag, you just insert the query you want to execute. If you save this in the appropriate directory, you can call it just like you would any XML file:

https://cordelia/tiger/nycounties.xml

Since the SQL in this template is identical to the string I put in the previous URL, it will return the same XML.
      Using a template file has several advantages. If you want to specify an XSL file, you can note it at the top of the XML file. For instance, you could automatically process the outgoing XML with nycounties.xsl by adding one line before the <root> tag:

<?xml-stylesheet type="text/xsl"
href="https://cordelia/XSLFiles/nycounties.xsl" ?>

By specifying the XSL file inside a template that the user never sees, they can't inadvertently get in trouble by indicating the wrong XSL file from within the URL query or by leaving it out completely.
Templates also let you set up more complex queries involving multiple datasets. Suppose you want to get a list of all the towns in Suffolk County, New York (county 103), as well as all the towns in Nassau County, New York (county 59). (For those unfamiliar with the area, this would produce all the towns on Long Island.) Table tgr361030c holds place information for Suffolk County, including all the towns within its borders. Any record where the fips column has a nonzero value is a town. (A FIPS code is a unique value assigned to any individual governmental entity, such as a town.) To generate a list of the town names, you can use this SQL:

SELECT name FROM tgr361030c WHERE fips != 0

SELECT name FROM tgr360590c WHERE fips != 0

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT name FROM tgr361030c
WHERE fips != 0 FOR XML RAW
</sql:query>
<sql:query>
SELECT name FROM tgr360590c
WHERE fips != 0 FOR XML RAW
</sql:query>
</root>

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Suffolk>
<sql:query>
SELECT name FROM tgr361030c WHERE fips != 0
FOR XML AUTO
</sql:query>
</Suffolk>
<Nassau>
<sql:query>
SELECT name FROM tgr360590c WHERE fips != 0
FOR XML AUTO
</sql:query>
</Nassau>
</root>

Note that I've moved the xmlns attribute to the <root> tag in both cases. It can go here instead of in the <sql:query> tag, so you don't have to reuse it in a case where you have more than one <sql:query>.      The first query template will produce the following XML data

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<row name="Amityville" />
<row name="Aquebogue" />
<row name="Asharoken" />
<row name="Babylon" />
���
<row name="Albertson" />
<row name="Atlantic Beach" />
<row name="Baldwin" />
<row name="Baldwin Harbor" />
���
</root>

while the second will produce this data:

<root xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<Suffolk>
<tgr361030c name="Amityville" />
<tgr361030c name="Aquebogue" />
<tgr361030c name="Asharoken" />
<tgr361030c name="Babylon" />
���
</Suffolk>
<Nassau>
<tgr360590c name="Albertson" />
<tgr360590c name="Atlantic Beach" />
<tgr360590c name="Baldwin" />
<tgr360590c name="Baldwin Harbor" />
���
</Nassau>
</root>

Of course, you probably want to format this output so that you can read it. This is another place where templates outstrip URL-based queries. Since the template is treated as an XML file (with a SQL namespace extension), you can add a line indicating a stylesheet to use to process the outgoing data:

<?xml-stylesheet type="text/xsl"
href="https://cordelia/XSLFiles/litowns.xsl" ?>

This makes the whole process a lot neater than indicating the XSL file as a URL parameter. 
      Once you get the XSL connection going, you can do some fascinating things with the data you're getting from the XML template. For instance, I've built a file called litowns.xsl that puts each of these town names in a listbox, then lets the user select one (see Figure 13). The data produced from the county-specific template file is easy to filter in this way if you use XSL. First, apply an XSL template on the root nodes. This will supply the information for a <select> tag.

<select size="8" id="lstTowns" name="lstTowns"
onchange="lstTowns_onchange()">
<xsl:apply-templates select="root" />
</select>

 I've selected all the data in XML RAW format, so each row of data will correspond to an XML tag named row. This XSL file matches each <row> tag.

<xsl:template match="root">
<xsl:apply-templates select="//row" />
</xsl:template>

Then the XSL file creates an <option> tag for each of these rows, setting the option's value to the FIPS code and the option's actual text to the name of the feature.

<xsl:template match="row">
<OPTION>
<xsl:attribute name="value">
<xsl:value-of select="@fips"/>
</xsl:attribute>
<xsl:value-of select="@name"/>
</OPTION>
</xsl:template>

Just to show that it's all working, I've also put a pair of <div> blocks on the page, and I set their values every time a user changes the selection within the listbox:

sub lstTowns_onchange()
vall = frmTowns.lstTowns.value
divValue.innerText = vall
divName.innerText =
frmTowns.lstTowns.item
(frmTowns.lstTowns.selectedIndex).text
end sub

      There's no reason to stop here. What if I want to retrieve all the segments for any particular town in this list? You can match individual line segments in the Type 1 data file with the town they're in by comparing the town's FIPS code with the feature's fpll and fplr columns. (fpll is the town on the left of the line, and fplr is the town to the right. You can query for all instances where fpll doesn't match fplr; this would give you a list of all town borders.) I could retrieve each item's unique identifier, name, and location with a SQL statement:

SELECT tlid,fedirs,fename,fetype,
frlat,frlong,tolat,tolong
FROM tgr3610301 WHERE fpll=(FIPS value)
OR fplr=(FIPS value)

      Since the FIPS value of the town I select is represented in the value of the listbox (frmTowns.lstTowns.value), this statement can be represented as a URL in code in a string like this:

"https://cordelia/tiger?sql=
SELECT+tlid,cfcc,fedirs,fename,
fetype,fpll,fplr,frlat,frlong,tolat,tolong+
FROM+tgr3610301+WHERE+fpll=
" + frmTowns.lstTowns.value + "
+OR+fplr=" +frmTowns.lstTowns.value + "
+FOR+XML+RAW"

      Since this URL-based request returns XML data, it can be used in conjunction with the Microsoft XMLDOM object to manipulate the incoming XML within scripting code. I've created a file named borders.xsl to filter this data (see Figure 14). In the page that's being generated by the code in Figure 13, I've added a div named divSegs and a button named btnVal.
      I can now create two XMLDom objects (one for the XML and one for the XSL):

dim xmlDoc, xslDoc
set xmlDoc = CreateObject("microsoft.xmldom")
set xslDoc = CreateObject("microsoft.xmldom")
xmlDoc.async = false
xslDoc.async = false

I can then retrieve the XML and the XSL by their URLs:

xmlDoc.load("https://cordelia/tiger?sql=SELECT+
tlid,cfcc,fedirs,fename,fetype,fpll,fplr,frlat,
frlong,tolat,tolong+FROM+tgr3610301+WHERE+fpll=" +
frmTowns.lstTowns.value + "+FOR+XML+RAW")
xslDoc.load("https://cordelia/XSLFiles/borders.xsl")

Finally, I can transform the XML with the XSL file I loaded, then replace the HTML code within the divSegs block with the output from this operation:

strout = xmlDoc.transformNode(xslDoc)
divSegs.innerHTML = strout

      The updated version of litowns.xsl is shown in Figure 15.

SQL Update Grams

      There's one feature of the SQL Server XML Technology Preview that I haven't used here, but which should be mentioned anyway. XML-based insert, update, and delete operations will be supported in the new release.
      If you're looking up some street data that was fetched through a template and you suddenly realize that the data is old and doesn't include the new road they've built through your favorite neighborhood park, you can add this road to the table with an XML-based update gram. This is supported with the element, in conjunction with the and elements. You specify the before and after data. If you specify only after data, it's assumed that you're doing an insert. If you specify only before data, you're doing a delete. If you provide both, you'll perform an update.
      This code inserts a new line segment into the table tgr3610301:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:sync>
<sql:after>
<tgr3610301 tlid="34755123" cfcc="A31"
fename="Goofy" fetype="St"
frlong="-74010800" frlat="40729400"
tolong="-74010700" tolat="40730200"/>
</sql:after>
</sql:sync>
<sql:query>SELECT * FROM tgr3610301 FOR XML AUTO
</sql:query>
</ROOT>

But wait a minute! Goofy Street is an obviously made-up road that shouldn't be in the table at all. Still not a problem; you can remove it through another gram. By creating a block with only a element, the corresponding data will be removed from the table. In this case I'll do it by using tlid, which is a unique identifier.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:sync>
<sql:before>
<tgr3610301 tlid="34755123"/>
</sql:before>
</sql:sync>
<sql:query>SELECT * FROM tgr3610301 FOR XML AUTO
</sql:query>
</ROOT>

      Finally, you can change this record's data by combining before and after elements. This will change the street name in the table from Goofy to Doofy:

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:sync>
<sql:before>
<tgr3610301 tlid="34755123" fename="Goofy"/>
</sql:before>
<sql:after>
<tgr3610301 tlid="34755123" fename="Doofy"/>
</sql:after>
</sql:sync>
<sql:query>SELECT * FROM tgr3610301 FOR XML AUTO
</sql:query>
</ROOT>

The Finishing Touches

      This is all quite useful, but now I'm going to go where no SQL Server 2000 demo has gone before: the land of graphics. After all, the information I've been playing with is well suited to street mapping, so why be content with mere table listings?
      I've developed a sample application with the SQL Server XML Technical Preview. It uses a combination of templates, XML support, XSL, and Vector Markup Language (VML). Since Microsoft Internet Explorer 5.0 is the only place you'll find support for VML and other technologies I'll be using, you'll need it to complete the demo. The server side has all the same requirements as the Technical Preview (IIS 4.0 or 5.0 and Windows NT 4.0, or Windows 2000 and SQL Server 7.0), and won't work with Personal Web Server.
      For this demo, I've imported the Tiger/LINE data for New York County, New York (also known as Manhattan) because it looks familiar to most people. Since New York County is county 61 in state 36, the database tables will be named tgr36061xx. I don't want every piece of information contained in the line segments fileâ€"streets and shorelines will do. So I'm only selecting records where the CFCC file starts with A, B, or H. (More information on the meanings of various CFCCs is available on the Tiger Web site.) A SQL statement like this one will get the relevant records:

SELECT cfcc,frlong,frlat,tolong,tolat
FROM tgr3606101 WHERE (cfcc LIKE 'A%')
OR (cfcc LIKE 'B%') OR (cfcc LIKE 'H%')
OR (countyl NOT LIKE countyr)
ORDER BY frlong, tolong

      Of course, every county has its own bounding box, defined by latitude and longitude. New York is bounded by the approximate coordinates 73.9076� W to 74.0437� W, and 40.6794� N to 40.8788� N. Because of the way measurements are stored in the Tiger databaseâ€"as integers with six decimal places of accuracyâ€"this translates to values like -74043700 and 40679400. Instead of going through tedious conversions for each line segment that comes through the XSL filter, it's a lot easier to set VML regions that use these approximate boundaries. To get the left, top, width, and height of this bounding box, you can use a SQL statement:

SELECT min(frlong),max(frlat),
max(frlong)-min(frlong),
max(frlat)-min(frlat) FROM tgr3606101

      The two previous statements provide all the data you need to start your mappin'! They can be combined into a single XML template like nycmap.xml (see Figure 16).
      The next phase is to write the multi.xsl file that was referenced in this template. (I called it multi.xsl because I've tried to remove all dependencies to individual county data; you can use it with any XML template that produces output like that of nycmap.xml.)
      Since I'm using VML in this sample, I have to declare the VML namespace as soon as possible in the output.

<HTML xmlns:v="urn:schemas-microsoft-com:vml" >

VML usually uses some simple coordinate set like pixels or inches, but you can redefine the coordinate space to something more suitable, like this:

<v:group style="position:relative;left:2pt;top:2pt;
width:3.5in;height:3.5in;"
coordorigin="aaa,bbb" coordsize="aaa,bbb">
(lines of VML instructions)
</v:group>

The coordorigin and coordsize attributes of this <v:group> tag can be set with boundary data coming right out of the Tiger table. The XML in Figure 16 already selects these values as part of the <measure> tag. When you create a computed column in an XML query, it is returned with the name ComputedColumnx, which is numbered starting with zero. So taking the values returned from nycmap.xml, you could construct XSL code to generate this <v:group> tag:

<v:group style="position:relative;left:2pt;top:2pt;
width:3.5in;height:3.5in;">
<xsl:attribute name="coordorigin">

<xsl:value-of select='//measure/row/
@ComputedColumn0' />,
<xsl:value-of select='//measure/row/
@ComputedColumn1' />
</xsl:attribute>
<xsl:attribute name="coordsize">
<xsl:value-of select='//measure/row/
@ComputedColumn2' />,
-<xsl:value-of select='//measure/row/
@ComputedColumn2' />
</xsl:attribute>

At runtime, this will result in output HTML that looks like this:

<v:group style="position:relative;left:2pt;top:2pt;
width:3.5in;height:3.5in;"
coordorigin="-74043700,40878832"
coordsize="136100,136100">

VML allows you to set negative origins on a <v:group>, but be careful. I've found that this functionality was broken in some beta versions of Windows 2000. (It worked again as of Release Candidate 3 and in retail versions.) There are also some VML objects that don't handle negative coordinates well, even when mapped against a negative coordinate system. The <v:rect> (rectangle) tag is one prime example because it doesn't accept a negative height tagâ€"a problem if you're mapping on a system where the measurements go from higher to lower. If you want to draw a shape like this on a negative coordinate system, you need to draw it from the bottom up, giving it a positive height value and drawing from what you would have considered the end backwards. Fortunately, the <v:line> element, which we'll be using here primarily, is cool with negative coordinates because it only needs a from and to set of numbers.
      The primary function of this sample is to draw one line for each record returned from XML. Since there's no coordinate conversion going on, this is a fairly simple process. Here's all the code you need to draw a line with VML:

<v:line strokecolor="red"
from="-73926400,40877700"
to="-73931655,40869196" />

Since this involves packing attributes into the <v:line> tag, you'll need to declare the line as an xsl:element, then insert a few <xsl:attribute> tags. A template that matches row tags can generate <v:line> elements like the previous sample by implementing the following XSL code:

<xsl:template match="row">
<xsl:element name="v:line">
<xsl:attribute name="strokecolor">
<xsl:eval>red</xsl:eval>
</xsl:attribute>
<xsl:attribute name="from">
<xsl:value-of
select="@frlong"/>,
<xsl:value-of
select="@frlat"/>
</xsl:attribute>
<xsl:attribute name="to">
<xsl:value-of
select="@tolong"/>,
<xsl:value-of
select="@tolat"/>
</xsl:attribute>
</xsl:element>
</xsl:template>

      Put this together with a couple of title lines and you'll get the file multimono.xsl, shown in Figure 17. When you browse to nycmap.xml with Microsoft Internet Explorer 5.0 or better, you'll get something that looks reasonably like a map (see Figure 18).

Figure 18The Big Apple

      There's one more step I'd like to introduce hereâ€"the selective drawing of line segments based on the feature type embedded into the XML. The linesegs data within the XML data looks like this:

<row cfcc="H01" frlong="-74043700" frlat="40689600"
tolong="-74043700" tolat="40689600"/>
<row cfcc="A31" frlong="-74010800" frlat="40729400"
tolong="-74010700" tolat="40730200"/>

      Obviously, it would be beneficial to draw the line segments with different colors depending on whether they represented water, a road, train tracks, or what have you. Every time a line segment is drawn in the browser, the XSL filter can first check to see what type it is. In the Microsoft implementation of XSL, you can evaluate expressions for inclusion in the output stream with the element. It's helpful to know that the this operator refers to the current XML node when you do this.
      By replacing the strokecolor attribute with this code

<xsl:attribute name="strokecolor">
<xsl:eval>QCFCC(this)
</xsl:eval></xsl:attribute>

you can call the function QCFCC to process the current node and return a preferred color based on its data. This function itself has only four lines. It checks the value of the cfcc attribute of the current node (q.attributes.getNamedItem("cfcc").nodeValue). If this value starts with the letter A, it means it's a road, so it's drawn in dark green. This can either be indicated with a standard DHTML color value like green or with a specific color value like #008000. Likewise, all water-based lines (starting with an H) are drawn in blue, and all railroad lines (starting with a B) are drawn in brown (#A0522D). If the line is none of these types (improbable because that's all nycmap.xml selects), draw it in basic black.

<xsl:script>

function QCFCC(q)
{
if ((q.attributes.getNamedItem(
"cfcc").nodeValue).
indexOf("A") == 0)
return "#008000";
if ((q.attributes.getNamedItem(
"cfcc").nodeValue).
indexOf("H") == 0)
return "#0000FF";
if ((q.attributes.getNamedItem(
"cfcc").nodeValue).
indexOf("B") == 0)
return "#A0522D";
return "black";
}

</xsl:script>

      The entire function is wrapped in an element, and additionally with a CDATA block. This makes sure that any characters that are legal in scripts but illegal in XSL aren't somehow misinterpreted. The final, colorful results can be seen in Figure 19; the new, improved XSL file that generates it is shown in Figure 20.

Figure 19Color-coded Map

      The entire application was made possible by a combination of XML and Internet Explorer 5.0, and the XML was made possible by the features in Microsoft SQL Server 2000. There are all sorts of things you could do to make this sample even betterâ€"zooming, street labeling, and so on. The data is freely redistributable, so go for it!

Background information: try
https://msdn.microsoft.com/xml/default.asp

Joshua Trupin is a technical editor for* MSDN Magazine . He has written numerous articles for MSJ and MIND , the predecessors to MSDN Magazine *, as well as a book, Hoop Stats: The Basketball Abstract. He lives on Long Island with his wife, two children, two dogs, and an estimated 1.4 million dust mites.

From the March 2000 issue of MSDN Magazine.