Inserting Order Details

Congratulations to Ihor Bobak, an MCP and chief software architect for UKEESS Software House in Lviv, Ukraine. Ihor won first prize of $100 for the best solution to the January Reader Challenge, "Inserting Order Details." Here's a recap of the problem and the solution to the January Reader Challenge.

Problem:

Rick is a database developer for a company that sells products online. SQL Server 2000 hosts the online transaction processing (OLTP) database, and customers place their orders through a Web services application that the company created. The OLTP database schema is similar to schema for the Northwind sample database, and order information is similar to data in Northwind's Orders and OrderDetails tables. Help Rick write a stored procedure that the Web services application can call to insert an order row, with details, into the database. The stored procedure needs to:

The following XML shows two valid fragments, one with shipping information and one without shipping information.

Solution:

Rick can use T-SQL's OPENXML rowset in SQL Server 2000 to process the XML information in rowset form, letting him obtain the order provider or order detail information from the XML document in the format he desires. To process the XML information using OPENXML, Rick must prepare the XML document by using the sp_xml_preparedocument system stored procedure, which creates an internal representation of the XML for processing. After obtaining the handle, a numeric value he can use to access the prepared XML information, Rick can use the handle with OPENXML in the SELECT statement's FROM clause to read data in relational or table format.

The code to read the order information from the sample XML code (without shipping information) is:

DECLARE @h int-- Prepare XML input for use:EXEC sp_xml_preparedocument @h out, '                  '-- Get order element from the XML that's mapped to the Orders table schema by using WITH:SELECT *  FROM OPENXML(@h, '/root/Order')  WITH "Orders"-- Remove prepared XML from memory:EXEC sp_xml_removedocument @hGO

In the preceding sample code, the OPENXML rowset provider produces a table that looks like the Orders table and fills the columns with values from the XML document's Order element. OPENXML's WITH clause also supports schema declaration for each column. Rick will use both mechanisms to solve his problem. For the Orders table, he uses the table approach becase the column values are provided in the XML document or can be nullable. The schema declaration helps Rick specify column information for XML attributes, then access them in SQL. (You can read the OPENXML section in SQL Server Books Online (BOL) for complete syntax rules and options.)

Rick can also use OPENXML to read the order details from the same XML document by using the following code.

DECLARE @h int-- Prepare XML input for use:EXEC sp_xml_preparedocument @h out, '                  '-- Get order details from the XML that's mapped to desired columns only:SELECT *  FROM OPENXML(@h, '/root/Order/OrderDetail' )  WITH (ProductID int, UnitPrice money, Quantity smallint, Discount real)-- Remove prepared XML from memory:EXEC sp_xml_removedocument @hGO

Schema declarations map the various XML OrderDetail element attributes to the table columns. You can't use the Order Details table to map the schema because the table's OrderID column isn't nullable because the value isn't known and the attribute representing the column isn't in the XML document.

Now, Rick can use the OPENXML features inside a stored procedure to get the order details in table format and process the details using regular SQL statements. The following stored procedure inserts the order and order detail information into the database from the XML document:

IF object_id('InsertOrderFromWeb') IS NOT NULL    DROP PROCEDURE InsertOrderFromWebGOCREATE PROCEDURE InsertOrderFromWeb(@Order_Xml text, @OrderID int = NULL output)ASBEGIN    DECLARE @h int, @retcode int    -- Prepare Order XML for OPENXML use:    EXEC @retcode = sp_xml_preparedocument @h OUTPUT, @Order_Xml    IF @@error | @retcode  0    BEGIN        RAISERROR('Invalid order xml!', 16, 2)        RETURN 1    END        BEGIN TRANSACTION WebOrderEntry    -- Extract order information from XML:    INSERT INTO "Orders"    (CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight,     ShipName, ShipAddress, ShipCity, ShipRegion,  ShipPostalCode, ShipCountry)    SELECT o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate,           o.ShippedDate, o.ShipVia, o.Freight, o.ShipName,           o.ShipAddress, o.ShipCity, o.ShipRegion,    o.ShipPostalCode, o.ShipCountry      FROM OPENXML(@h, '/root/Order' )      WITH "Orders" AS o    IF @@error  0 GOTO undo    -- Get new order id value:    SET @OrderID = SCOPE_IDENTITY()    -- Extract order details from XML:    INSERT INTO "Order Details"    (OrderID, ProductID, UnitPrice, Quantity, Discount)    SELECT @OrderID, od.ProductID, od.UnitPrice, od.Quantity, od.Discount      FROM OPENXML(@h, '/root/Order/OrderDetail' )      WITH (ProductID int, UnitPrice money, Quantity smallint, Discount real) AS od    IF @@error  0 GOTO undo    COMMIT TRANSACTION WebOrderEntry        -- Remove Order XML from memory:    EXEC sp_xml_removedocument @h    RETURN 0    undo:    IF @@trancount > 0 ROLLBACK TRANSACTION WebOrderEntry    -- Remove Order XML from memory:    EXEC sp_xml_removedocument @h    RETURN 2            ENDGO

The stored procedure starts a user-defined transaction, performs the inserts into the Orders and Order Details tables, and returns the newly generated order ID value as the output parameter. The OrderID column in the Orders table is an IDENTITY column. The stored procedure retrieves the newly generated OrderID value by using the SCOPE_IDENTITY() system function. The SCOPE_IDENTITY() system function returns the most recently generated IDENTITY value in the session for a column in a table.

FEBRUARY READER CHALLENGE:

Now, test your SQL Server savvy in the February Reader Challenge, "Importing from Excel" (below). Submit your solution in an email message to [email protected] by January 22. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Phil generates reports for a sales team in a company that sells books. The company stores publication data for its books in a SQL Server 2000 database. Phil receives sales data updates in a Microsoft Excel file that has the following header labels for columns: stor_id, yr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec. The spreadsheet contains quantity of books sold in each store by year and month. You can generate sample data for the Excel spreadsheet from the Sales table in the Pubs database by using the following code:

SELECT s.stor_id, year(s.ord_date) AS yr,       SUM(CASE month(s.ord_date) WHEN 1 THEN s.qty ELSE 0 END) AS Jan,       SUM(CASE month(s.ord_date) WHEN 2 THEN s.qty ELSE 0 END) AS Feb,       SUM(CASE month(s.ord_date) WHEN 3 THEN s.qty ELSE 0 END) AS Mar,       SUM(CASE month(s.ord_date) WHEN 4 THEN s.qty ELSE 0 END) AS Apr,       SUM(CASE month(s.ord_date) WHEN 5 THEN s.qty ELSE 0 END) AS May,       SUM(CASE month(s.ord_date) WHEN 6 THEN s.qty ELSE 0 END) AS Jun,       SUM(CASE month(s.ord_date) WHEN 7 THEN s.qty ELSE 0 END) AS Jul,       SUM(CASE month(s.ord_date) WHEN 8 THEN s.qty ELSE 0 END) AS Aug,       SUM(CASE month(s.ord_date) WHEN 9 THEN s.qty ELSE 0 END) AS Sep,       SUM(CASE month(s.ord_date) WHEN 10 THEN s.qty ELSE 0 END) AS Oct,       SUM(CASE month(s.ord_date) WHEN 11 THEN s.qty ELSE 0 END) AS Nov,       SUM(CASE month(s.ord_date) WHEN 12 THEN s.qty ELSE 0 END) AS Dec  FROM Sales AS s GROUP BY s.stor_id, year(s.ord_date)

Phil needs to import the data from the Excel file into a SQL Server table called StoreSalesSummary by unpivoting the month columns from the spreadsheet. You can create the StoreSalesSummary table by using the following code:

CREATE TABLE StoreSalesSummary (    stor_id int NOT NULL,    qty int NOT NULL,    yr smallint NOT NULL,    mn tinyint NOT NULL,    PRIMARY KEY(stor_id, yr, mn))

Help Phil import only the Excel spreadsheet data into the StoreSalesSummary table, then insert and update each store's sales from the spreadsheet. Import only the stores with a nonzero quantity value for any month.