数据库中操作XML（openXML）

最近公司项目需要在数据库中操作XML，因此系统的学习了一下

一、openxml的格式

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

参数含义

idoc XML 文档的内部表式形式的文档句柄。通过调用sp_xml_preparedocument 创建 XML 文档的内部表式形式。

XPath 模式，用来标识要作为行处理的节点。

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

用sql语句准备一段XML

declare @idoc int
declare @doc nvarchar(1000)
set @doc=N'<root>
<customer cid="c1" name="Janine" city="Issaquah">
   <order oid="01" data="1/20/1996" amount="3.5"/>
   <order oid="02" data="4/30/1997" amount="13.4">Customer was very satisfied</order>
</customer>
<customer cid="c2" name="Ursula" city="Oelde">
   <order oid="03" data="7/14/1999" amount="100" note="Wrap it blue white red" >
      <Urgency>Impotant</Urgency>
  </order>
    <order oid="04" date="1/20/1996" amount="10000"/>
</customer>
</root>'
exec sp_xml_preparedocument @idoc out,@doc

--declare @temp char(5)
select * from openxml(@idoc,'/root/customer/order',0)
with (oid char(5),--属性
      Urgency varchar(50),--元素
      commont ntext '@mp:xmltext'
      )

Flags为0时使用“以属性为中心”的映射

Flags为1时使用“以属性为中心”的映射，这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。

Flags为2时使用“以元素为中心”的映射，这种情况下，首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射。

Flags为3时 1和2是可以组合使用的，组合的同时1和2的结果都可以显示

若属性名字与元素名字相同，那么在这种情况下则是首先应用“以属性为中心”的映射，然后对所有未处理的列应用“以元素为中心”的映射即先显示属性再显示元素

Flags为8时在检索的上下文中，该标志指示不应将已使用的数据复制到溢出属性 @mp:xmltext

如果不指定WITH子句，查询出来的是一个默认的表结构，如下：

当flag=2 却仍然需要显示属性时可以用如下代码

select * from openxml(@idoc,'/root/customer/order',2)
with (oid1 char(5) '@oid',
      oid char(5),--属性
      Urgency varchar(50),--元素      
      commont ntext '@mp:xmltext'
      )

显示如下

即只要在with的最后一个参数中，加上XML中想要的标签名称就可以了这里要注意路径