使用sp_xml_preparedocument处理XML文档 保存到sqlserver中

有时会在存储过程中处理一些XML格式的数据，所以会用到sp_xml_preparedocument，他可以将XML数据进行读取，然后使用 MSXML 分析器 (Msxmlsql.dll) 对其进行分析。我们就可以很容易的在存储过程中得到XML中我们想要的数据。下面的代码就是使用sp_xml_preparedocument读取XML：

DECLARE @hdoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot">   <Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">      <OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>      <OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>   </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">   <Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">      <OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>   </Order></Customer></ROOT>'EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc

上面只是读取了XML，要想获取XML数据还需要使用OPENXML，代码如下：

SELECT *FROM openxml(@hdoc,'/ROOT/Customer',1)WITH (CustomerID VARCHAR(40),ContactName VARCHAR(40)) 

OPENXML有三个参数：

• 第一个是sp_xml_preparedocument读取是的OUTPUT参数，在本示例中就是@hdoc；
• 第二个是一个XPath表达式，用来获取指定位置的数据；
• 第三个是一个可选项，用来表示获取的方式，有0,1,2,8四种取值，详细解释请看

FROM后面的WITH也是可选的，用来指定获取哪些数据字段，上面代码中只取了CustomerID和ContactName。上面的查询结果如下： CustomerID                               ContactName ---------------------------------------- ---------------------------------------- VINET   Paul Henriot LILAS                                    Carlos Gonzlez 如果不指定WITH子句，查询出来的是一个默认的表结构，如下:

表格列的解释说明：

列名数据类型说明idbigint文档节点的唯一 ID。 根元素的 ID 值为 0。保留负 ID 值。parentidbigint标识节点的父节点。此 ID 标识的父节点不一定是父元素。具体情况取决于此 ID 所标识节点的子节点的节点类型。例如，如果节点为文本节点，则其父节点可能是一个属性节点。 如果节点位于 XML 文档的顶层，则其 ParentID 为 NULL。节点类型int标识节点类型，是对应于 XML 对象模型 (DOM) 节点类型编号的一个整数。 下列值是可以显示在此列中以指明节点类型的值： 1 = 元素节点 2 = 属性节点 3 = 文本节点 4 = CDATA 部分节点 5 = 实体引用节点 6 = 实体节点 7 = 处理指令节点 8 = 注释节点 9 = 文档节点10 = 文档类型节点 11 = 文档片段节点 12 = 表示法节点 有关详细信息，请参阅 Microsoft XML (MSXML) SDK 中的“节点类型属性”主题。localnamenvarchar(max)提供元素或属性的本地名称。如果 DOM 对象没有名称，则为 NULL。prefixnvarchar(max)节点名称的命名空间前缀。namespaceurinvarchar(max)节点的命名空间 URI。如果值是 NULL，则命名空间不存在。datatypenvarchar(max)元素或属性行的实际数据类型，否则是 NULL。数据类型是从内联 DTD 中或从内联架构中推断得出。prevbigint前一个同级元素的 XML ID。如果前面没有同级元素，则为 NULL。textntext包含文本形式的属性值或元素内容。如果边缘表项不需要值则为 NULL。

在WITH子句中,我们还可以通过设置来获取父级元素的属性值:

DECLARE @hdoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot">   <Order OrderID="10248" CustomerID="VINET" EmployeeID="5"           OrderDate="1996-07-04T00:00:00">      <OrderDetail ProductID="11" Quantity="12"/>      <OrderDetail ProductID="42" Quantity="10"/>   </Order></Customer><Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">   <Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"           OrderDate="1996-08-16T00:00:00">      <OrderDetail ProductID="72" Quantity="3"/>   </Order></Customer></ROOT>'EXEC sp_xml_preparedocument @hdoc OUTPUT, @docSELECT *FROM   OPENXML (@hdoc, '/ROOT/Customer/Order/OrderDetail',2)         WITH (OrderID       int         '../@OrderID',               CustomerID  varchar(10) '../@CustomerID',               OrderDate   datetime    '../@OrderDate',               ProdID      int         '@ProductID',               Qty         int         '@Quantity')

查询的结果为: OrderID     CustomerID OrderDate               ProdID      Qty ----------- ---------- ----------------------- ----------- ----------- 10248       VINET      1996-07-04 00:00:00.000 11          12 10248       VINET      1996-07-04 00:00:00.000 42          10 10283       LILAS      1996-08-16 00:00:00.000 72          3 有时候XML中的数据并不是以属性的方式存在,而是直接放在节点中,如下:

DECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot">   <Order>     <OrderID>10248</OrderID>     <CustomerID>VINET</CustomerID>     <EmployeeID>5</EmployeeID>     <OrderDate>1996-07-04T00:00:00</OrderDate>   </Order></Customer></ROOT>'

此时要获Order节点下的各项的值,可以用下面方法:

DECLARE @hdoc intDECLARE @doc varchar(1000)SET @doc ='<ROOT><Customer CustomerID="VINET" ContactName="Paul Henriot">   <Order>     <OrderID>10248</OrderID>     <CustomerID>VINET</CustomerID>     <EmployeeID>5</EmployeeID>     <OrderDate>1996-07-04T00:00:00</OrderDate>   </Order></Customer></ROOT>'EXEC sp_xml_preparedocument @hdoc OUTPUT, @docSELECT *FROM   OPENXML (@hdoc, '/ROOT/Customer/Order',1)WITH (OrderID       int         'OrderID',   CustomerID  varchar(10) 'CustomerID',   EmployeeID   int    'EmployeeID',   OrderDate      datetime         'OrderDate')

查询结果如下: OrderID     CustomerID EmployeeID  OrderDate ----------- ---------- ----------- ----------------------- 10248       VINET      5           1996-07-04 00:00:00.000 可以看出是取属性值还是取节点的文本的值区别在于WITH子句的第三个参数是否有@符号