最近公司项目需要在数据库中操作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中想要的标签名称就可以了 这里要注意路径