SQL处理XML
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL处理XML，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9566字，纯文字阅读大概需要14分钟。
内容图文
使用SP_XML_PREPAREDOCUMENT 来读取 Xml 文本。
读取作为输入提供的 XML 文本，然后使用 MSXML 分析器 (Msxmlsql.dll) 对其进行分析，并提供分析后的文档供使用。分析后的文档对 XML 文档中的各节点（元素、属性、文本和注释等）的树状表示形式。
为格式正确的 XML 文档准备内部表示形式
-- 第一个参数：
-- 新创建文档的句柄。@hdoc 是一个整数。
DECLARE @hdoc INT
-- 第二个参数：
-- 是原来的XML 文档。MSXML 分析器分析该XML 文档。
-- @doc 是一个文本参数：char、nchar、varchar、nvarchar、text、ntext 或xml。
-- 默认值为NULL，在此情况下将创建一个空XML 文档的内部表示形式。
DECLARE @doc VARCHAR(1000)
-- 第三个参数：
-- [ 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 有效。
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
EXEC SP_XML_REMOVEDOCUMENT @hdoc
需要注意的地方
- 返回 0 成功，>0失败。需要具有Public角色的成员身份。
- 分析后的文档存储在 SQL Server 2005 的内部缓存中。MSXML 分析器占用 SQL Server 可用总内存的八分之一。若要避免内存不足，请运行 SP_XML_REMOVEDOCUMENT 以释放内存。
- sp_xml_preparedocument 将可以同时打开的最大元素数限制为 256。
使用OPENXML 通过 XML 文档提供行集视图
语法：
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
窗体的架构定义：ColName ColType [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）。
提示：通过使用 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）。 |
使用带 OPENXML 的简单 SELECT 语句
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>‘
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT
*
FROM OPENXML(@idoc, ‘/ROOT/Customer‘,1)
WITH
(
CustomerID varchar(10),
ContactName varchar(20)
)
执行结果：
|
CustomerID |
ContactName |
|
VINET |
Paul Henriot |
|
LILAS |
Carlos Gonzlez |
以上使用 sp_xml_preparedocument 创建 XML 图像的内部表示形式。然后对 XML 文档的内部表示形式执行使用 OPENXML 行集提供程序的 SELECT 语句。
flag 值设置为 1。该值指示"以属性为中心"的映射。因此，XML 属性映射到行集中的列。指定为 /ROOT/Customer 的 rowpattern 标识要处理的 <Customers> 节点。
没有指定可选的 ColPattern（列模式）参数，因为列名与 XML 属性名称匹配。
OPENXML 行集提供程序创建了一个双列行集（CustomerID 和 ContactName），SELECT 语句从该行集中检索必要的列。
为列和 XML 属性之间的映射指定 ColPattern
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‘)
执行结果：
|
OrderID |
CustomerID |
OrderDate |
ProdID |
Qty |
|
10248 |
VINET |
1996-7-4 |
11 |
12 |
|
10248 |
VINET |
1996-7-4 |
42 |
10 |
|
10283 |
LILAS |
1996-8-16 |
72 |
3 |
上面的查询从 XML 文档返回客户 ID、订单日期、产品 ID 和数量等属性。rowpattern 标识 <OrderDetails> 元素。ProductID 和 Quantity 是 <OrderDetails> 元素的属性。而 OrderID、CustomerID 和 OrderDate 是父元素 (<Orders>) 的属性。
指定可选的 ColPattern。这包括以下各项：
- 行集中的 OrderID、CustomerID 和 OrderDate 映射到 XML 文档中的 rowpattern 所标识节点的父节点属性。
- 行集中的 ProdID 列映射到 ProductID 属性，行集中的 Qty 列映射到 rowpattern 中所标识节点的 Quantity 属性。
尽管"以元素为中心"的映射由 flags 参数指定，但 ColPattern 中指定的映射的优先级高于该映射。
再看看另外一个：
DECLARE @idoc int
DECLARE @doc varchar(1000)
SET @doc =‘
<ROOT>
<Customer>
<CustomerID>1</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order CustomerID="1" EmployeeID="5">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>2</CustomerID>
<ContactName>Carlos Gonzlez</ContactName>
<Order CustomerID="2" EmployeeID="3">
<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‘,2)
WITH (-- CustomerID int,
ContactName varchar(20),
CustomerID int ‘Order/@CustomerID‘,
EmployeeID int ‘Order/@EmployeeID‘,
OrderID INT ‘Order/OrderDetail/@OrderID‘,
ProductID INT ‘Order/OrderDetail/@ProductID‘,
Quantity INT ‘Order/OrderDetail/@Quantity‘
)
EXEC sp_xml_removedocument @idoc
执行结果：
|
ContactName |
CustomerID |
EmployeeID |
OrderID |
ProductID |
Quantity |
|
Paul Henriot |
1 |
5 |
10248 |
11 |
12 |
|
Carlos Gonzlez |
2 |
3 |
10283 |
72 |
3 |
这个结果只有两行的纪录，这是因为选择XPath不同，上面这个选择是/ROOT/Customer所以取的时候是按这个XPath为主的。
转自：http://www.cnblogs.com/sherrys/archive/2007/06/07/775511.html
SQL处理XML
标签：
本文系统来源：http://www.cnblogs.com/zhang9418hn/p/4551656.html
内容总结
以上是互联网集市为您收集整理的SQL处理XML全部内容，希望文章能够帮你解决SQL处理XML所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。