SqlServer 解析xml文件（demo版）-蒲公英云

目的：利用SqlServer解析xml文件，相对于poi优点传输速度快（体积小），减少查询直接在数据库操作

一、SQL代码

DECLARE @idoc int
DECLARE @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>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM   OPENXML (@idoc, '/ROOT/Customer/Order/OrderDetail',2)
         WITH (OrderID       int         '../@OrderID',
               CustomerID  varchar(10) '../@CustomerID',
               OrderDate   datetime    '../@OrderDate',
               ProdID      int         '@ProductID',
               Qty         int         '@Quantity')

二、结果

OpenXml用法

OPENXML( idoc int [ in] , rowpattern nvarchar [ in ] , [ flags byte [ in ] ] ) 
[ WITH ( SchemaDeclaration | TableName ) ]

说明：

通过使用 SchemaDeclaration 或指定一个现有 TableName，WITH 子句提供一种行集格式（根据需要还可提供其他映射信息）。如果没有指定可选的 WITH 子句，则以“边缘”表格式返回结果。边缘表在单个表中表示 XML 文档的细密结构（例如，元素/属性名、文档层次结构、命名空间、处理说明等）。

下表介绍了“边缘”表的结构。

列名	数据类型	说明
id	bigint	文档节点的唯一 ID。根元素的 ID 值为 0。保留负 ID 值。
parentid	bigint	标识节点的父节点。此 ID 所标识的父节点不一定是父元素，而是取决于此 ID 所标识节点的子节点的 NodeType。例如，如果节点是文本节点，则其父节点可能是属性节点。如果节点位于 XML 文档的顶层，则其 ParentID 为 NULL。
nodetype	int	标识节点类型。一个对应于 XML DOM 节点类型编号的整数。节点类型包括： 1 = 元素节点 2 = 属性节点 3 = 文本节点
localname	nvarchar	给出元素或属性的本地名称。如果 DOM 对象没有名称，则为 NULL。
prefix	nvarchar	节点名称的命名空间前缀。
namespaceuri	nvarchar	节点的命名空间 URI。如果值为 NULL，则命名空间不存在。
datatype	nvarchar	元素或属性行的实际数据类型，否则为 NULL。从内联 DTD 中或从内联架构中推断数据类型。
prev	bigint	前一个同级元素的 XML ID。如果前面没有同级元素，则为 NULL。
text	ntext	包含文本格式的属性值或元素内容（如果“边缘”表项不需要值，则为 NULL）。

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。