SQL Server2005中使用XML-OPENXML
使用open xml将别人的xml数据插入到自己的数据库中
DECLARE @doc xml
SET @doc = '
-
714
5
28.84
-
715
1
30
'
DECLARE @docHandle int
--调用系统存储过程以创建内存树
(sp_xml_preparedocument解析XML文档,在内存中形成一棵树,将树的根节点指针作为句柄传回;后面的程序,通过句柄进行操作。)
EXECsp_xml_preparedocument@docHandleOUTPUT, @doc
-----------------------------------------------------------------------------
INSERT[Sales].[SalesOrderHeader]
([CustomerID], [DueDate], [AccountNumber], [ContactID], [BillToAddressID],
[ShipToAddressID], [ShipMethodID], [SubTotal], [TaxAmt])
SELECT * FROM
--添加OPENXML查询(返回一张表)插入数据到SalesOrderHeader表
OPENXML(@docHandle, '/SalesOrder', 1)
--访问/SalesOrder节点,0代表默认映射(属性),1代表节点的属性,2代表节点的文本,3代表属性和文本
WITH--定义表的结构
(CustomerIDint,
DueDatedatetime,
AccountNumbernvarchar(15),
ContactIDint,
BillToAddressID int,
ShipToAddressID int,
ShipMethodIDint,
SubTotalmoney,
TaxAmtmoney)
-----------------------------------------------------------------------------
DECLARE @result int
--获取SalesOrderHeader插入的特性以使用在SalesOrderDetail插入中
SET @result = SCOPE_IDENTITY()
INSERT INTO[Sales].[SalesOrderDetail]
([SalesOrderID], [OrderQty], [ProductID], [SpecialOfferID], [UnitPrice])
SELECT@result, [OrderQty], [ProductID], 1, [UnitPrice]FROM
--添加OPENXML查询插入数据到SalesOrderDetail表
OPENXML(@docHandle, '/SalesOrder/Item', 2)
--访问/SalesOrder/Item节点,1代表节点的属性,2代表节点的文本
WITH
(OrderQtyint,
ProductIDint,
UnitPricemoney)
--调用存储过程以清除内存树
EXEC sp_xml_removedocument @docHandle