Browse DevX
Sign up for e-mail newsletters from DevX


Store and Retrieve XML-based Business Objects with SQL Server and .NET

Combine SQL Server's XML support and .NET XML serialization to simplify your front-end code by moving custom objects rather than DataSets between application layers and into and out of SQL Server.

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.

XML Queries
To retrieve the results of a query in XML format, use the FOR XML clause in your queries—write 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

Here's the XML document returned by the preceding query.

   <Order ID="10912" CustID="HUNGO" 
      <Detail Price="21.0000" 
      <Detail Price="123.7900" 

The XML document contains Order and Detail 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 <row> element.


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.

Thanks for your registration, follow us on our social networks to keep up-to-date