Browse DevX
Sign up for e-mail newsletters from DevX


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

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Controlling the Schema of the XML Response
The syntax of the query determines the structure of the XML document that will be generated by SQL Server and therefore the structure of the class that describes the data. The preceding query retrieves the bare minimum of information needed to describe an order; for example, to complete the information you would have to look up the product names. Similarly, there's no information about the customer who placed the order, beyond a meaningless customer ID. To solve that problem, add a join to the Products and Customers tables so you can retrieve the names of the products that appear in the order, as well as the name of the company and a company contact. The following code excerpt shows a GetOrderByID stored procedure that returns an order, including product and customer information.

CREATE PROCEDURE GetOrderByID @OrderID int AS SELECT [Order].OrderID AS ID, [Order].Freight, [Order].CustomerID AS CustomerID, CONVERT(datetime, [Order].OrderDate ) AS OrderDate, CONVERT(datetime, [Order].ShippedDate ) AS ShipDate, (SELECT CompanyName FROM Customers WHERE Customers.CustomerID= [Order].CustomerID) AS Customer, (SELECT ContactName FROM Customers WHERE Customers.CustomerID= [Order].CustomerID) AS Contact, Detail.ProductID, Detail.ProductName, CAST(Details.UnitPrice AS numeric(8,2)) As Price, CAST(Details.Quantity AS int) AS Quantity, CAST(Details.Discount AS numeric(8,2)) AS Discount FROM [Order Details] Details INNER JOIN Orders [Order] ON [Order].OrderID = Details.OrderID INNER JOIN Products Detail ON Details.ProductID = Detail.ProductID WHERE [Order].OrderID = @OrderID FOR XML AUTO

To make the two columns of the Order Details table used in the query (the UnitPrice, Quantity and Discountcolumns) attributes of the element, rather than separate elements, you must cast the columns into their own type, and SQL Server will associate them with the preceding table (element) name in the query. Note also that the Products table is aliased as Detail, because this is the name of the element we want to see in the XML output. The XML document returned by the GetOrderByID stored procedure is shown here:

<Order ID="10900" Freight="1.6600" CustomerID="WELLI" OrderDate="1998-02-20T00:00:00" ShipDate="1998-03-04T00:00:00" Customer="Wellington Importadora" Contact="Paula Parente"> <Detail ProductID="70" ProductName="Outback Lager" Price="15.00" Quantity="3" Discount="0.25"/> </Order>

Now, the XML document's root element is and contains as many elements as there are detail lines in the order. All the column values appear as attributes of these two elements. You can include columns from other tables as attributes of an existing element by inserting a subquery in the selection list. The subquery does not introduce another element in the XML document; instead, it creates a new attribute in the preceding element. The GetOrderByID stored procedure shown earlier uses subqueries to retrieve customer information. The subqueries that select columns from the Customers table must appear below the Order table's fields in the selection list.

Sometimes, you need even more control over the XML format than simple placement can provide. Using the XML EXPLICIT option provides even more flexibility, but you may find that the query syntax becomes too convoluted. XML EXPLICIT queries are hard to write and even harder to read. Still, if your query is too complicated to be implemented efficiently with the techniques discussed here, you can always resort to the explicit formulation of the XML document.

When you're satisfied with the XML format returned by a query, you can create a class whose field structure reflects the document content.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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