SQL Server 2000 and XML

[Previous] [Next]

As we write this, Microsoft hasn't said much about what's going to be in the next major release—version 2000—of its flagship database product, which of course is SQL Server. You can expect quite a lot of new features in it; what we're going to describe to you here is just one of them. The feature we're going to talk very briefly about is the XML support SQL Server 2000 will provide, or at least the part of XML support that Microsoft has opened up about. This new support consists really of two capacities, but they're very interesting together: they'll allow you to access SQL Server from a Web page, using HTTP, and to get XML in return. Here's an example of such a call:

 http://server_1/racing?sql=SELECT+Horsename+FROM+Horses+FOR+XML+Auto 

In the preceding SQL Server call, server_1 of course is the name of the server called. /racing refers to a virtual root directory named racing. The question mark indicates that what's coming after it is a parameter, and the parameter itself is an SQL SELECT statement with a FOR XML Auto clause trailing it. Each + sign indicates a space; the SQL statement is in fact a URL, and you can't use spaces in URLs. The following is the partial result of such a call:

 <?xml version="1.0" encoding="UTF-8" ?> <root> <Horses Horsename="Get Rhythm" /> <Horses Horsename="Gyzette" /> <Horses Horsename="Grevens Tid" /> </root> 

Even more interesting, if the SELECT statement contains a JOIN clause and if the tables joined together are also joined in the database with a foreign key constraint, SQL Server returns a hierarchical XML document. The following is the partial result of such a call:

 <?xml version="1.0" encoding="UTF-8" ?> <root> <Trainers Firstname="Tommy" Lastname="Gustafsson"> <Horses Horsename="Get Rhythm" /> <Horses Horsename="Gyzette" /> <Horses Horsename="Grevens Tid" /> </Trainers> <Trainers Firstname="Michael" LastName="Kahn"> <Horses Horsename="Homosassa" /> <Horses Horsename="Itsabrahma " /> <Horses Horsename="Mr. Eubanks" /> </Trainers> </root> 

SQL Server 2000 is not yet a released product, so we can tell you only a little more about its extremely useful capability of not only returning but also accepting update grams that specify update operations that the database should perform. An update gram is an XML document, and SQL Server 2000 can accept it just as if it were an UPDATE, an INSERT, or a DELETE statement. Let us briefly give you an example of an update gram to help you understand what's ahead.

 <root xmlns:sql="urn:schemas-microsoft-com:xml-sql"> <sql:sync> <sql:after> <Horses HorseId="9797" Horsename="Old Faithful" Sex="h" …> </sql:after> </sql:sync> </root> 

The preceding update gram inserts Old Faithful in the Horses table of the database. You can see that from the fact that there's only an sql:after element without an sql:before element. SQL Server 2000 interprets this as a request for an insert operation. A request for an update operation would have an sql:before element as well as an sql:after element. The sql:before element would describe the "before" image of a specific horse; the sql:after element, the desired result after the update.

A request for a delete operation would have an sql:before element, describing the horse (or whatever) that should be deleted from the database table. It would also have an sql:after element, but an empty one to describe the desired result after the deletion.

When you read this, SQL Server 2000 might very well be available, allowing you to investigate these capabilities and others yourself. If it isn't, we recommend that you download "Microsoft SQL Server XML Technology Preview," which covers at least part of the XML functionality in SQL Server 2000. You'll find it at the following address:

http://msdn.microsoft.com/workshop/xml/articles/xmlsql/sqlxml_prev.asp



Designing for scalability with Microsoft Windows DNA
Designing for Scalability with Microsoft Windows DNA (DV-MPS Designing)
ISBN: 0735609683
EAN: 2147483647
Year: 2000
Pages: 133

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net