mssql openxml函数用法


openxml功能简介

使用openxml sql函数,可以对mssql中缓存的xml结构的数据,进行相应的解析,返回指定格式的行集数据。



openxml语法简介

openxml语法格式如下:

openxml (idoc int(输入参数),’节点匹配 nvarchar’,flag byte)
[with (表结构)|表名]


参数说明:
idoc : 由sp_xml_preparedocument 生成的xml缓存句柄
节点匹配: 用来待处理节点的xml文档位置
flag:
0 默认为以属性为中心映射。
1 使用以属性为中心映射。 可以与 XML_ELEMENTS 一起使用。 在这种情况下,以属性为中心首先,应用的映射,然后以元素为中心映射应用未得到处理的所有列。
2 使用以元素为中心映射。 可以与 XML_ATTRIBUTES 一起使用。 在这种情况下,以属性为中心首先,应用的映射,然后以元素为中心映射应用的所有列未都处理。
8 可与 XML_ATTRIBUTES 或 XML_ELEMENTS 组合使用(逻辑或)。 在检索上下文中,此标志指示消耗的数据不应复制到溢出属性 @mp:xmltext 。
with:
可选参数
表结构: 可以定义一些表结构所对应的列名,例:(CustomerID varchar(10), ContactName varchar(20))
表名 : 如果系统已经存在类似表结构定义,那么此时我们只需要输入表名既可


默认返回表结构说明:

 DECLARE @idoc int  
DECLARE @xmlInfo nvarchar(1000)  
SET @xmlInfo = N'< ROOT>  
< Customer CustomerID="customerId1" ContactName="联系人1">
   < CustomerID>客户Id1
   < Order OrderID="订单Id" CustomerID="客户Id3" EmployeeID="员工Id"   
          OrderDate="1996-07-04T00:00:00">节点中文本
      < OrderDetail ProductID="产品Id1" Quantity="数量1"/>  
      < OrderDetail ProductID="产品Id2" Quantity="数量2"/>  
   < /Order>  
  
< Customer CustomerID="客户Id4" ContactName="联系人2"> 
   < Order OrderID="订单ID2" CustomerID="客户Id5" EmployeeID="员工Id2"   
          OrderDate="1996-08-16T00:00:00">  
      < OrderDetail ProductID="产品Id3" Quantity="产品数量3">  
     
  
'  
----将xml文本缓存至sql db中,并返回相应的句柄  
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlInfo  

---利用 缓存信息 行集化xml信息 

---返回所有信息
SELECT *  
FROM OPENXML (@idoc, '/ROOT/Customer',1)    
 
----释放缓存信息
EXEC sp_xml_removedocument @idoc 



id: bigint 文档返回唯一标识值
parentid:bigint 父级节点标识值 如果节点位于xml顶端,则返回null ,如果节点为文本,则返回属性节点的Id
nodetype: int (1:元素节点 2:属性节点 3:文本节点)
localname: nvarchar 元素或属性的名称,不存在名称,则返回null
prefix: nvarchar 节点名称的命名空间前缀。
namespaceuri: 节点的命名空间 uri。如果值为 null,则命名空间不存在。
datatype: nvarchar 元素或属性行的实际数据类型,否则为 null。从内联 dtd 中或从内联架构中推断数据类型。
prev: bigint 前一个同级元素的 xml id。如果前面没有同级元素,则为 null。
text: ntext 包含文本格式的属性值或元素内容(如果”边缘”表项不需要值,则为 null)。


openxml 举例说明

 DECLARE @idoc int  
DECLARE @xmlInfo nvarchar(1000)  
SET @xmlInfo = N'  
< Customer CustomerID="customerId1" ContactName="联系人1">
   < CustomerID>客户Id1
   < Order OrderID="订单Id" CustomerID="客户Id3" EmployeeID="员工Id"   
          OrderDate="1996-07-04T00:00:00">节点中文本
      < OrderDetail ProductID="产品Id1" Quantity="数量1"/>  
      < OrderDetail ProductID="产品Id2" Quantity="数量2"/>  
     
  
< Customer CustomerID="客户Id4" ContactName="联系人2"> 
   < Order OrderID="订单ID2" CustomerID="客户Id5" EmployeeID="员工Id2"   
          OrderDate="1996-08-16T00:00:00">  
      < OrderDetail ProductID="产品Id3" Quantity="产品数量3">  
     
  
'  
----将xml文本缓存至sql db中,并返回相应的句柄  
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlInfo  

---利用 缓存信息 行集化xml信息 

---返回节点属性
SELECT *  
FROM OPENXML (@idoc, '/ROOT/Customer',1)    
 with(CustomerID varchar(30),    ---指定节点名称
      ContactName varchar(60),    
      [Order] varchar(100)
 ) 
---读取元素信息
SELECT *  
FROM OPENXML (@idoc, '/ROOT/Customer',2)    
 with(CustomerID varchar(30),    ---指定元素名称
      ContactName varchar(60),    
      [Order] varchar(100)
 ) 
----释放缓存信息
EXEC sp_xml_removedocument @idoc