© 2004 Microsoft Corporation. All rights reserved.

Figure 1 The XML Data Access Architecture

Figure 1 The XML Data Access Architecture
Figure 4 The Sample ASP Pages

Page
Function
login.asp
Requests the user to enter his/her employee ID. Other pages redirect to this page if no ID has been entered
empdetail.asp
Invokes the cWriter object's EmployeeDetail method and writes the result to the browser using the Response.Write method. This creates a page showing the employee's orders that they have created
orddetail.asp
Invokes the cWriter object's OrderDetail method and writes the result to the browser using the Response.Write method. This creates a page showing the detail of a specific order
neworder.asp
Invokes the cWriter object's CreateOrder method and writes the result to the browser using the Response.Write method. This creates a page that provides a list of customers that will be selected from to associate a new order with
additem.asp
Invokes the cWriter object's AddOrderItem method and writes the result to the browser using the Response.Write method. This creates a page that provides a list of products to add to an order
Figure 5 cWriter Methods

Method
Invoked by
Use
AddOrderItem
additem.asp page
Determines whether the user is beginning, updating, or completing the order process for the current order
CreateOrder
neworder.asp page
Determines whether the user has selected a customer to process the order for. When they have, the order is created in the database and the user is redirected to the orderdetail.asp page
EmployeeDetail
empdetail.asp page
Prepares a list of orders that the logged-on employee has taken. Order IDs are hyperlinked to the detailed information page for each order
EmployeeLogin
login.asp page
Checks to make sure the supplied employee ID exists in the database and then redirects the user to their employee detail (empdetail.asp) page
OnStartPage
When the class is instantiated on an ASP page loaded by IIS
Used to capture a reference to the current scripting context
OrderDetail
orddetail.asp page
Lists the employee and customer information along with a list of individual products that make up an order
Figure 6 The Employee_GetDetail Stored Procedure
CREATE PROCEDURE Employee_GetDetail
   @EmployeeID AS INT
AS

—Set the return name for the dataset name that will get returned and used
—as the document level of the DOM object
SELECT "EmployeeDetail" AS DataSet

—specify the employee detail level of the DOM
SELECT "Employees" AS NextResultset, "Employee" AS RecordType

—return the records that will make up the record portion of the DOM
SELECT EmployeeID, LastName, FirstName, Title, TitleOfCourtesy
FROM Employees
WHERE EmployeeID = @EmployeeID

—specify the orders that the employee is associated with
SELECT "Orders" AS NextResultset, "Order" AS RecordType

—return the detailed order records
SELECT ord.OrderID, ord.CustomerID, cus.CompanyName, 
   CONVERT(NVARCHAR, ord.OrderDate, 101) AS OrderDate,
   CONVERT(NVARCHAR, ord.ShippedDate, 101) AS ShippedDate,
   Total = CAST(CAST(SUM(det.Quantity * (1 - det.Discount) * 
      det.UnitPrice) AS DECIMAL(10,2)) AS NVARCHAR)
FROM Orders ord JOIN Customers cus
   ON cus.CustomerID = ord.CustomerID
   JOIN [Order Details] det
   ON det.OrderID = ord.OrderID
WHERE ord.EmployeeID = @EmployeeID
GROUP BY ord.OrderID, ord.OrderID, ord.CustomerID, cus.CompanyName, 
   ShippedDate, ord.OrderDate
ORDER BY ord.OrderID DESC
Figure 7 Results of Employee_GetDetail
<?xml version="1.0"?>
<EmployeeDetail xmlns:dt="urn:schemas-microsoft-com:datatypes">
  <Employees>
    <Employee>
      <EmployeeID dt:dt="i4">1</EmployeeID>
      <LastName dt:dt="string">Davolio</LastName>
      <FirstName dt:dt="string">Nancy</FirstName>
      <Title dt:dt="string">Sales Representative</Title>
      <TitleOfCourtesy dt:dt="string">Ms.</TitleOfCourtesy>
    </Employee>
  </Employees>
  <Orders>
    <Order>
      <OrderID dt:dt="i4">11077</OrderID>
      <CustomerID dt:dt="string">RATTC</CustomerID>
      <CompanyName dt:dt="string">Rattlesnake 
        Canyon Grocery</CompanyName>
      <OrderDate dt:dt="string">05/06/1998</OrderDate>
      <ShippedDate dt:dt="string"></ShippedDate>
      <Total dt:dt="string">1255.72</Total>
    </Order>
    <Order>
      <OrderID dt:dt="i4">11071</OrderID>
      <CustomerID dt:dt="string">LILAS</CustomerID>
      <CompanyName dt:dt="string">LILA-Supermercado</CompanyName>
      <OrderDate dt:dt="string">05/05/1998</OrderDate>
      <ShippedDate dt:dt="string"></ShippedDate>
      <Total dt:dt="string">484.50</Total>
    </Order>
  </Orders>
</EmployeeDetail>
Figure 8 New Order Stored Procedure
CREATE PROCEDURE Order_CreateOrder
   @EmployeeID AS INT,
   @CustomerID AS NCHAR(5)
AS

—variable to hold the order id for the record we are going to create
DECLARE @OrderID AS INT

—insert a new order record and return the ID that was generated
—by the system
INSERT Orders (EmployeeID, CustomerID) VALUES (@EmployeeID, @CustomerID)

—get the order ID that we just generated
SET @OrderID = @@IDENTITY

—now we are going to update the record we just created with the shipping
—information from the customer
UPDATE Orders SET ShipName = cus.CompanyName, ShipAddress = cus.Address,
   ShipCity = cus.City, ShipRegion = cus.Region, 
   ShipPostalCode = cus.PostalCode, ShipCountry = cus.Country,
   OrderDate = GETDATE()
FROM Customers cus
WHERE cus.CustomerID = @CustomerID AND Orders.OrderID = @OrderID
   
—return the id of the order that we just created
RETURN @OrderID