昨天和今天都在为一个ASP.NET数码商品购物网站问题困惑。本网站代码的思路是将网页上提交采购需求存储为XML文档,并且将其中XML数据以字符串形式传送给数据库底层程序处理。下面是存储过程upOrdersAdd,里面运用到事务,比较容易看懂,这个存储过程作用是订单提交之后在数据库中作相应的操作,表Orders,LineItem,OrderStatus 这三个表需要插入记录,Inventory这个表是商品数量表,需要更新。USE [D:\MIKECATCSHARP\CHAPTER10\DS\APP_DATA\DIGITALSHOP.MDF]
GO
/****** 对象: StoredProcedure [dbo].[upOrdersAdd] 脚本日期: 06/07/2008 23:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-------------------------------------------------------------------------------
-- upOrdersAdd
-------------------------------------------------------------------------------
/******************************************************************************
Add order to database. Example of using this stored proc is shown below.
declare @xml varchar(8000)
set @xml =
'
<Orders
userid="j2ee" orderdate="1/1/2001"
shipaddr1="1234 West Branch" shipaddr2=""
shipcity="Liveoak" shipstate="Texas"
shipzip="12345" shipcountry="USA"
billaddr1="5678 East Branch" billaddr2="Building C"
billcity="Madrone" billstate="Utah"
billzip="99999" billcountry="USA"
courier="UPS" totalprice="57.50"
billtofirstname="Fred" billtolastname="Derfy"
shiptofirstname="Bob" shiptolastname="Black"
creditcard="111-222-333" exprdate="9/2002"
cardtype="Visa" locale="US_en">
<LineItem itemid="EST-1" linenum="1" quantity="4" unitprice="5.00" />
<LineItem itemid="EST-2" linenum="2" quantity="5" unitprice="7.00" />
<LineItem itemid="EST-3" linenum="3" quantity="2" unitprice="1.25" />
</Orders>
' exec upOrderAdd @xml
*******************************************************************************/
ALTER PROCEDURE [dbo].[upOrdersAdd]
(
@xml varchar(8000)
)
AS DECLARE @idoc int -- xml doc
DECLARE @orderid int -- new order -- parse xml doc
EXEC sp_xml_preparedocument @idoc output, @xml
SET NOCOUNT ON
DECLARE @CurrentError int -- start transaction, updating three tables
BEGIN TRANSACTION -- add new order to Orders table
INSERT INTO Orders
SELECT userid, orderdate, shipaddr1, shipaddr2, shipcity, shipstate,
shipzip, shipcountry, billaddr1, billaddr2, billcity, billstate, billzip,
billcountry, courier, totalprice, billtofirstname, billtolastname,
shiptofirstname, shiptolastname, creditcard, exprdate, cardtype, locale
FROM OpenXML(@idoc, '/Orders')
WITH Orders -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- get new order id
SELECT @orderid = @@IDENTITY -- add line items to LineItem table
INSERT INTO LineItem
SELECT @orderid, linenum, itemid, quantity, unitprice
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- add status to OrderStatus table
INSERT INTO OrderStatus
SELECT @orderid, @orderid, getdate(), 'P' -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- update inventory
UPDATE Inventory
SET Inventory.qty = Inventory.qty - LineItem.quantity
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem
WHERE Inventory.itemid=LineItem.itemid -- check for error
select @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- end of transaction
COMMIT TRANSACTION SET NOCOUNT OFF -- done with xml doc
EXEC sp_xml_removedocument @idoc -- return the new order
RETURN @orderid ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0 我运行程序购物后的程序中的XML内容如下:
"<Orders userid='zhanghuan' orderdate='2008-6-7 22:45:34' shiptofirstname='张' shiptolastname='欢' shipaddr1='南京市江宁区' shipaddr2='' shipcity='南京' shipstate='New York' shipzip='211100' shipcountry='Canada' billtofirstname='张' billtolastname='欢' billaddr1='南京市江宁区' billaddr2='' billcity='南京' billstate='New York' billzip='211100' billcountry='Canada' creditcard='9999 9999 9999 9999' exprdate='01/2008' cardtype='交通银行太平洋卡' courier='UPS' totalprice='18.5' locale='US_en'><LineItem itemid='EST-12' linenum='1' quantity='1' unitprice='18.5' /></Orders>"
但是出现了异常,异常内容如下:
"无法绑定由多个部分组成的标识符 \"LineItem.itemid\"。"这个问题我弄了一天都没有解决,知道的大虾们帮助我一下,100分相送
GO
/****** 对象: StoredProcedure [dbo].[upOrdersAdd] 脚本日期: 06/07/2008 23:54:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO-------------------------------------------------------------------------------
-- upOrdersAdd
-------------------------------------------------------------------------------
/******************************************************************************
Add order to database. Example of using this stored proc is shown below.
declare @xml varchar(8000)
set @xml =
'
<Orders
userid="j2ee" orderdate="1/1/2001"
shipaddr1="1234 West Branch" shipaddr2=""
shipcity="Liveoak" shipstate="Texas"
shipzip="12345" shipcountry="USA"
billaddr1="5678 East Branch" billaddr2="Building C"
billcity="Madrone" billstate="Utah"
billzip="99999" billcountry="USA"
courier="UPS" totalprice="57.50"
billtofirstname="Fred" billtolastname="Derfy"
shiptofirstname="Bob" shiptolastname="Black"
creditcard="111-222-333" exprdate="9/2002"
cardtype="Visa" locale="US_en">
<LineItem itemid="EST-1" linenum="1" quantity="4" unitprice="5.00" />
<LineItem itemid="EST-2" linenum="2" quantity="5" unitprice="7.00" />
<LineItem itemid="EST-3" linenum="3" quantity="2" unitprice="1.25" />
</Orders>
' exec upOrderAdd @xml
*******************************************************************************/
ALTER PROCEDURE [dbo].[upOrdersAdd]
(
@xml varchar(8000)
)
AS DECLARE @idoc int -- xml doc
DECLARE @orderid int -- new order -- parse xml doc
EXEC sp_xml_preparedocument @idoc output, @xml
SET NOCOUNT ON
DECLARE @CurrentError int -- start transaction, updating three tables
BEGIN TRANSACTION -- add new order to Orders table
INSERT INTO Orders
SELECT userid, orderdate, shipaddr1, shipaddr2, shipcity, shipstate,
shipzip, shipcountry, billaddr1, billaddr2, billcity, billstate, billzip,
billcountry, courier, totalprice, billtofirstname, billtolastname,
shiptofirstname, shiptolastname, creditcard, exprdate, cardtype, locale
FROM OpenXML(@idoc, '/Orders')
WITH Orders -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- get new order id
SELECT @orderid = @@IDENTITY -- add line items to LineItem table
INSERT INTO LineItem
SELECT @orderid, linenum, itemid, quantity, unitprice
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- add status to OrderStatus table
INSERT INTO OrderStatus
SELECT @orderid, @orderid, getdate(), 'P' -- check for error
SELECT @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- update inventory
UPDATE Inventory
SET Inventory.qty = Inventory.qty - LineItem.quantity
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem
WHERE Inventory.itemid=LineItem.itemid -- check for error
select @CurrentError = @@Error IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END -- end of transaction
COMMIT TRANSACTION SET NOCOUNT OFF -- done with xml doc
EXEC sp_xml_removedocument @idoc -- return the new order
RETURN @orderid ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0 我运行程序购物后的程序中的XML内容如下:
"<Orders userid='zhanghuan' orderdate='2008-6-7 22:45:34' shiptofirstname='张' shiptolastname='欢' shipaddr1='南京市江宁区' shipaddr2='' shipcity='南京' shipstate='New York' shipzip='211100' shipcountry='Canada' billtofirstname='张' billtolastname='欢' billaddr1='南京市江宁区' billaddr2='' billcity='南京' billstate='New York' billzip='211100' billcountry='Canada' creditcard='9999 9999 9999 9999' exprdate='01/2008' cardtype='交通银行太平洋卡' courier='UPS' totalprice='18.5' locale='US_en'><LineItem itemid='EST-12' linenum='1' quantity='1' unitprice='18.5' /></Orders>"
但是出现了异常,异常内容如下:
"无法绑定由多个部分组成的标识符 \"LineItem.itemid\"。"这个问题我弄了一天都没有解决,知道的大虾们帮助我一下,100分相送
其实XML
说明了就是数据库的表
象<HRML>一样
不过只有一个根
<HTML>
<HEAD></HEAD>
<BODY></BODY>
</HTML>
最外层的<HTML>就相当于表
<HEAD><BODY>相当于表的字段
和操作表是一样的
只不过要用操作XML的语句