SqlServer 解析xml文件（demo版）

清疚 2022-05-26 07:16 280阅读 0赞

目的：利用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')

二、结果

![70][]

**OpenXml用法**

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

**说明：**

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

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

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

**idoc**

*XML 文档的内部表式形式的文档句柄。通过调用 sp\_xml\_preparedocument 创建 XML 文档的内部表式形式。*

**rowpattern**

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

**flags**

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

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

**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 节点的信息（如相对位置和命名空间信息）。它提供了比文本表示形式更详细的信息。