• 售前

  • 售后

热门帖子
入门百科

利用sp_xml_preparedocument处理XML文档的方法

[复制链接]
睿智奔腾小马 显示全部楼层 发表于 2021-10-26 14:23:38 |阅读模式 打印 上一主题 下一主题
有时会在存储过程中处理一些XML格式的数据,所以会用到sp_xml_preparedocument,他可以将XML数据举行读取,然后使用 MSXML 分析器 (Msxmlsql.dll) 对其举行分析。我们就可以很容易的在存储过程中得到XML中我们想要的数据。下面的代码就是使用sp_xml_preparedocument读取XML:
复制代码 代码如下:
DECLARE @hdoc 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 @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 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>'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
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 int
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>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc
SELECT *
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子句的第三个参数是否有@符号

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作