SQL把XML字段解析成表

DECLARE @idoc int
DECLARE @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>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM  OPENXML (@idoc, '/ROOT/Customer',1)
      WITH (CustomerID  varchar(10),
            ContactName varchar(20))

 

sp_xml_preparedocument语法：

注意

分析后的文档存储在 SQL Server 的内部缓存中。MSXML 分析器占用 SQL Server 可用总内存的八分之一。若要避免内存不足，请运行 sp_xml_removedocument 以释放内存。

sp_xml_preparedocument
hdoc
OUTPUT
[ , xmltext ]
[ , xpath_namespaces ]

 

参数

hdoc

    新创建文档的句柄。hdoc 是一个整数。

[ xmltext ]

    是原来的 XML 文档。MSXML 分析器分析该 XML 文档。xmltext 是一个文本参数：char、nchar、varchar、nvarchar、text、ntext 或 xml。默认值为 NULL，在此情况下将创建一个空 XML 文档的内部表示形式。

 

[ xpath_namespaces ]

指定在 OPENXML 的行和列 XPath 表达式中使用的命名空间声明。xpath_namespaces 是一个文本参数：char、nchar、varchar、nvarchar、text、ntext 或 xml。

默认值为 <root xmlns:mp="urn:schemas-microsoft-com:xml-metaprop">。xpath_namespaces 通过格式正确的 XML 文档为在 OPENXML 中的 XPath 表达式中使用的前缀提供命名空间 URI。xpath_namespaces 声明必须使用前缀来引用命名空间 urn:schemas-microsoft-com:xml-metaprop；这将提供有关分析的 XML 元素的元数据。虽然可以使用这项技术来为元属性命名空间重新定义命名空间前缀，但该命名空间不会丢失。即使 xpath_namespaces 不包含这类声明，前缀 mp 依然对 urn:schemas-microsoft-com:xml-metaprop 有效。

 

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	使用“以属性为中心”的映射。
2	使用“以元素为中心”的映射。
8	可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用（逻辑或）。