All the hype that once surrounded XML is finally starting to die down, and developers are really beginning to harness the power and flexibility of the language. XML is a data descriptive language that uses a set of user-defined tags to describe data in a hierarchically structured format.
The release of Microsoft SQL Server 2000 saw Microsoft jump on the XML band-wagon too: they've included a number of different ways to manipulate data as well-formed XML. Firstly, there's the SQL XML support. Microsoft's implementation of SQL XML provides a simple configuration tool that allows developers to gain remote access to databases using URL based queries over HTTP. For example, we can setup an SQL XML virtual directory on our Web server named "myVirtual". Then, assuming we have the appropriate security permissions, we can use any browser to query our database using a simple URL based query (such as: http://www.myserver.com/myVirtual?SQL=select+*+from+products+for+xml+auto). This then returns our results as a valid XML document.
Notice the "for xml auto" part of our query above?
This determines the way in which SQL Server 2000 "shapes"
our data. There are three shaping methods:
It's the third method, "for xml explicit", that I will discuss today. The explicit method, in my opinion, is the most powerful feature of SQL Server 2000. Not only can we specify how XML data is returned to us, but we can also use record filters and sorting patterns as well, because, as we all know, sorting an XML document any other way is almost impossible.
Now, let's get into it. This article is aimed at the intermediate to advanced developer looking to use XML in the BLL (business logic layer) of an n-Tier based application where speed is a critical issue. To benefit from this article, you'll need to equip yourself with the following: