Retrieving Data as XML from SQL Server
By: Mitchell Harper October 1st, 2001 Reader Rating: 8
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. XMLis a data descriptive language that uses a set of user-defined tags to describe data in a hierarchicallystructured format.
About the Author
Mitchell is the lead developer for Interspire, who develop re-brandable Internet software and tools to help Web developers increase their
customer base and make more revenue. The release of Microsoft SQL Server 2000 a couple of monthsago saw Microsoft jump on the XML Mitchell can be reached via email at band-wagon too - they've included a number of different ways to manipulate data as well-formed XML. firstname.lastname@example.org. Firstly, there's the SQL XML support. Microsoft's implementation of SQL XML provides a simple View all articles by Mitchell Harper... configuration tools that allows developers to gain remote access todatabases 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 an XMLbased recordset.
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: 1. "for xml auto": Returns XML elements that are nested, based on which tables are listed in the "from" part of the query, and which fields are listed in the "select" part. 2. "for xml raw": Returns XML elements with the "row" prefix(ex: ""). Each column in a table is represented as an attribute and null column values aren't included. 3. "for xml explicit": Explicit mode is the most complex shaping method used in SQL Server 2000. It allows users to query a data source in such a way that the names and values of the returned XML are specified before the query batch is executed. It's this third method, "for xml explicit", thatI will discuss today. The explicit method, in my opinion, is the most powerful feature of SQL Server 2000. Not only can we specify how our 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 advanceddeveloper who's 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: A Win2k box running IIS and SQL Server 2000 with XML support Basic ASP, SQL, XML and XSL knowledge
Step 1: Creating our sample database
On your SQL Server 2000 server, open EnterpriseManager and create a new database named "myProducts". Then, using either Enterprise Manager, or Query Analyser, create the tables shown below:
(Note: catId, productId and descId are all auto-incrementing identity fields) As you've probably guessed, we're using three tables to simulate a very simple product description database (let's assume we sell books). The diagram
1 de 5
3/2/2010 14:23Sitepoint : New Articles, Fresh Thinking for Web Developers and Desi...
above shows the hierarchy of our data: categories listing products, listing their descriptions. Before we progress to the next step, we'll need to create some dummy data in our tables. To maximise productivity and minimise the length of this article, I've...