SQL Server2005中使用XML－使用OPENXML将xml数据插入到数据库

微软帮助文档地址：http://msdn.microsoft.com/zh-cn/library/ms186918.aspx

OPENXML 通过 XML 文档提供行集视图。由于 OPENXML 是行集提供程序，因此可在会出现行集提供程序（如表、视图或 OPENROWSET 函数）的 Transact-SQL 语句中使用 OPENXML。

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

参数

idoc

XML 文档的内部表式形式的文档句柄。通过调用 sp_xml_preparedocument 创建 XML 文档的内部表式形式。

rowpattern

XPath 模式，用来标识要作为行处理的节点（这些节点在 XML 文档中，该文档的句柄由 idoc 参数传递）。

flags

指示应在 XML 数据和关系行集间使用映射以及应如何填充溢出列。flags 为可选输入参数，可以是下列值之一：

字节值	说明
0	默认为“以属性为中心”的映射。
1	使用“以属性为中心”的映射。可以与 XML_ELEMENTS 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。
2	使用“以元素为中心”的映射。可以与 XML_ATTRIBUTES 一起使用。这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。
8	可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑或）。在检索的上下文中，该标志指示不应将已使用的数据复制到溢出属性 @mp:xmltext。

SchemaDeclaration

窗体的架构定义：ColNameColType [ColPattern | MetaProperty] [,ColNameColType [ColPattern | MetaProperty]...]

ColName

行集中的列名。

ColType

行集中列的 SQL Server 数据类型。如果列类型不同于属性的基础 xml 数据类型，则将发生类型强制。

ColPattern

可选的通用 XPath 模式，它说明应如何将 XML 节点映射到列。如果没有指定 ColPattern，则发生默认映射（由 flags 指定的“以属性为中心”或“以元素为中心”的映射）。

指定为 ColPattern 的 XPath 模式用于指定特殊的映射性质（如果发生“以属性为中心”和“以元素为中心”的映射），这些特殊的映射性质可以重写或增强由 flags 所指示的默认映射。

指定为 ColPattern 的通用 XPath 模式也支持元属性。

MetaProperty

由 OPENXML 提供的元属性之一。如果指定 MetaProperty，则该列包含元属性提供的信息。使用元属性可以提取有关 XML 节点的信息（如相对位置和命名空间信息）。它提供了比文本表示形式更详细的信息。

TableName

如果具有所需架构的表已经存在且不要求列模式，则为给定的表名（而不是 SchemaDeclaration）。

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