etting database data into and out of business objects has always been a problem; however, now that SQL Server 2000 supports XML, you can use interesting techniques that capitalize on .NET's object serialization capabilities to retrieve XML-formatted data from SQL Server and transform it into custom class instances.
People commonly think of the XML returned from SQL Server as documents. If you take that approach, you can use existing techniques for converting existing stored procedures into Web services and transforming the XML documents using XSLT. However, another approach is to think of the returned XML as an XML-serialized object. The object represented by the XML document has never been properly serialized, so you don't have the definition of the class that generated the XML data. Fortunately, it's straightforward to construct a class that matches the schema of the data returned and to deserialize the result of a query into an instance of that class. The classes you construct in this manner represent business objects and you can use them to pass data between tiers.
The technique discussed in this article combines several key technologies of the .NET framework, and demonstrates clearly how (and why) Microsoft used XML as the common substrate for much of .NET. The article describes the process required to build a sample application that retrieves orders from the Northwind database and storing them in business objects that represent orders rather than simply putting the data in DataSets that contain related tables. This approach simplifies the coding of the presentation tier, because developers can work with business objects rather than abstract data.
To retrieve the results of a query in XML format, use the FOR XML clause in your querieswrite a SQL query as usual and append the clause at the end of the query. For example, the following query retrieves an order from the Northwind database in XML format:
SELECT [Order].OrderID AS ID,
[Order].CustomerID As CustID,
Detail.UnitPrice As Price,
Detail.Quantity AS Quantity,
Detail.Discount AS Discount
FROM [Order Details] Detail
INNER JOIN Orders [Order] ON
[Order].OrderID = Detail.OrderID
WHERE [Order].OrderID = 10912
FOR XML AUTO
Here's the XML document returned by the preceding query.
<Order ID="10912" CustID="HUNGO"
The XML document contains Order
elements that correspond to the two tables accessed by the query. Each table's fields have become attributes of the table's element. Note that the query uses As
alias names so that SQL Server outputs the desired element and attribute names. The last argument in the FOR XML clause (AUTO
in the query example) determines the shape of the XML document, and takes one of the following values:
Maps each row in the result set into a
The result set is transformed into a
nested XML tree. Each table in the FROM clause is mapped to an XML element and
each column is mapped to an attribute.
The structure of the XML tree is specified
explicitly by the query, which must be written in a particular way.