How to use OpenXml to import xml data to Sql server
SQL Server2005中使用XML－OPENXML
<I got this article from others, but I forget where I saw this.>
使用open xml将别人的xml数据插入到自己的数据库中
DECLARE @doc xml
SET @doc = '<?xml version="1.0" ?>
<SalesOrder CustomerID="18759" DueDate="2006-01-01T00:00:00"
AccountNumber="10-4030-018759" ContactID="4189" BillToAddressID="14024" ShipToAddressID="14024" ShipMethodID="1" SubTotal="174.20" TaxAmt="10">
<Item>
<ProductID>714</ProductID>
<OrderQty>5</OrderQty>
<UnitPrice>28.84</UnitPrice>
</Item>
<Item>
<ProductID>715</ProductID>
<OrderQty>1</OrderQty>
<UnitPrice>30</UnitPrice>
</Item>
</SalesOrder>'
DECLARE @docHandle int
-- 调用系统存储过程以创建内存树
（sp_xml_preparedocument解析XML文档，在内存中形成一棵树，将树的根节点指针作为句柄传回；后面的程序，通过句柄进行操作。）
EXEC sp_xml_preparedocument @docHandle OUTPUT, @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 --定义表的结构
( CustomerID int,
DueDate datetime,
AccountNumber nvarchar(15),
ContactID int,
BillToAddressID int,
ShipToAddressID int,
ShipMethodID int,
SubTotal money,
TaxAmt money)
-----------------------------------------------------------------------------
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
( OrderQty int,
ProductID int,
UnitPrice money)
-- 调用存储过程以清除内存树
EXEC sp_xml_removedocument @docHandle
