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:
- Take order details in XML format
- Return the order ID that the processing system generates
- Validate the XML that the Web services application submits
- Enable systems that produce the XML (in two formats: one with shipping information and one without, as the Web version of this problem shows) to use the stored procedure
The following XML shows two valid fragments, one with shipping information and one without shipping information.
<!-- Without shipping information -->
<root>
<Order CustomerID="PARIS"
EmployeeID="1" OrderDate="2003-08-04T00:00:00"
RequiredDate="2003-08-04T00:00:00">
<OrderDetail ProductID="61" UnitPrice="28.5"
Quantity="12" Discount=""/>
<OrderDetail ProductID="62" UnitPrice="49.3"
Quantity="7" Discount="0.07"/>
</Order>
</root>
<!-- With shipping information -->
<root>
<Order CustomerID="PARIS" EmployeeID="1"
OrderDate="2003-08-04T00:00:00"
RequiredDate="2003-08-04T00:00:00"
ShipCity="Seattle" ShipCountry="USA">
<OrderDetail ProductID="11" UnitPrice="22.000" Quantity="12" Discount=""/>
<OrderDetail ProductID="42" UnitPrice="56.000" Quantity="7" Discount="0.07"/>
</Order>
</root>
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, '
<!-- Without shipping information. -->
<root>
<Order CustomerID="PARIS" EmployeeID="1"
OrderDate="2003-08-04T00:00:00"
RequiredDate="2003-08-04T00:00:00">
<OrderDetail ProductID="61"
UnitPrice="28.5" Quantity="12" Discount=""/>
<OrderDetail ProductID="62"
UnitPrice="49.3" Quantity="7" Discount="0.07"/>
</Order>
</root>'
-- 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 @h
GO
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, '
<!-- Without shipping information. -->
<root>
<Order CustomerID="PARIS" EmployeeID="1"
OrderDate="2003-08-04T00:00:00" RequiredDate=
"2003-08-04T00:00:00">
<OrderDetail ProductID="61" UnitPrice="28.5"
Quantity="12" Discount=""/>
<OrderDetail ProductID="62" UnitPrice="49.3"
Quantity="7" Discount="0.07"/>
</Order>
</root>'
-- 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 @h
GO
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 InsertOrderFromWeb
GO
CREATE PROCEDURE InsertOrderFromWeb
(@Order_Xml text, @OrderID int = NULL output)
AS
BEGIN
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
END
GO
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.
