sqlserver xml 操作：3、对不确定结构、属性的xml进行处理
sqlserver xml 操作：3、对不确定结构、属性的xml进行处理
比如，日常我们对内容进行查询时，会有很多很复杂的条件，而通常，我们需要根据条件来拼接sql指令，例如：查询资讯内容，条件有1周内，属于教育行业的，属于北京地区的，属于图书馆方面的。
通常，我们就需要很多if来进行判断，例如
string sql = "select * from 资讯表 where 在线状态=1 and 删除状态=0"
#region 条件追加
if (条件包含时间范围){
sql += " and datediff(d,发布时间,getdate())<=时间范围（天数）"
}
if (条件包含地区){
sql += " and area in (北京)"
}
if (条件包含行业){
....
}
.....
#endregion
DataTable dt = DB.GetDataTableBySql(sql);总之，条件越多，这个拼接就越长，如果是用存储过程来获取数据，那就可能需要不确定的参数个数来实现这个查询，好烦躁！
于是，使用xml来代替多个参数实现查询就成为一种可选的解决方案
例如，我直接给存储过程传递一个xml，这个xml中包含不确定多的参数，例如
<args 时间范围="7天" 地区="北京,上海" 行业="教育,培训" 场景="图书馆" />- 1
只要将所有的条件都扔到xml里，用存储过程对xml解析，并在存储过程中拼接sql就会好很多，为什么这么说呢？因为调用这同一个信息的地方会有很多页面，条件大差不差的都差不多，但每个页面写一次拼接，哪有在数据库里用存储过程只写一遍的舒服，扩展起来也方便，不用每个页面再维护一次。
所以，我们来处理一下这个xml吧。
ps：其实，之前在使用一个存储过程完成数据插入和更新（使用xml）（通用insert和update）(mssql2008以上)一文中，已经有过这个操作了，有兴趣的同学可以去看看哦。
首先，我们不确定xml到底有几个属性，其次，根节点名我们都忽略掉，怎么拿出数据呢，用 openxml 来操作。
declare @xml xml,@handle int,@prepare int
set @xml = '<args 时间范围="7天" 地区="北京,上海" 行业="教育,培训" 场景="图书馆" />'
exec @prepare = sp_xml_preparedocument @handle output,@xml
select * from openxml(@handle,'/*',1)
可以看到，openxml 可以直接将xml文档，解析成一个无限级的树状数据表，根据id和parentid得到层级关系，根据nodetype得到数据类型（节点1、属性2、值3），localname得到节点名、属性名，text就是值
so，我们来个关联查询，得到所有属性，忽略掉根节点
declare @xml xml,@handle int,@prepare int
set @xml = '<args 时间范围="7天" 地区="北京,上海" 行业="教育,培训" 场景="图书馆" />'
exec @prepare = sp_xml_preparedocument @handle output,@xml
;with t as (
select * from openxml(@handle,'/*',1)
)
select a.localname,b.text
from t a
left join t b on a.id=b.parentid
where a.nodetype=2
然后将这个结果保存到临时表中，再进行sql拼接
declare @sql nvarchar(max)
select @sql = 'select * from 资讯 where 在线=1 and 删除=0'
if exists(select 1 from #临时表 where localname='时间范围')
begin
....
end
if exists(select 1 from #临时表 where localname='地区')
if exists(select 1 from #临时表 where localname='行业')
if exists(select 1 from #临时表 where localname='场景')
if exists(select 1 from #临时表 where localname='搜索词')
if exists(select 1 from #临时表 where localname='来源')
.....
exec(@sql)so，我们只需要定义一个最完整的条件解析，其他调用的地方就完全不用考虑了
而之前讲的通用插入和更新，其实也可以扩展一下哦，例如，更新一个资讯，同时更新这个资讯表相关的图片表、分类表之类的，那么，提交给存储过程的xml可以这么写
<submit>
<资讯表 标题="测试" 作者="文盲老顾" 来源="csdn博客文章" 正文="梦中呓语，不知所谓，鸡同鸭讲，风马牛不相及" result_pk="id" />
<资讯分类关联表 资讯="@result_pk" 分类="博客,技术文章,sqlserver,xml" />
</submit>将需要更新的内容放到一个xml里，并对每个更新内容指定表名以及对应的主键，很容易就能实现需要很多指令才能做到的事。先看看xml解析后，都返回了什么
declare @xml xml,@handle int,@prepare int
set @xml = '<submit>
<资讯表 标题="测试" 作者="文盲老顾" 来源="csdn博客文章" 正文="梦中呓语，不知所谓，鸡同鸭讲，风马牛不相及" result_pk="id" />
<资讯分类关联表 资讯="@result_pk" 分类="博客,技术文章,sqlserver,xml" />
</submit>'
exec @prepare = sp_xml_preparedocument @handle output,@xml
select * from openxml(@handle,'/*',1)
嗯，对数据处理一下，做个关联查询后看看结果
declare @xml xml,@handle int,@prepare int
set @xml = '<submit>
<资讯表 标题="测试" 作者="文盲老顾" 来源="csdn博客文章" 正文="梦中呓语，不知所谓，鸡同鸭讲，风马牛不相及" result_pk="id" />
<资讯分类关联表 资讯="@result_pk" 分类="博客,技术文章,sqlserver,xml" />
</submit>'
exec @prepare = sp_xml_preparedocument @handle output,@xml
;with t as (
select * from openxml(@handle,'/*',1)
)
select b.localname as 表名,c.localname as 字段名,d.text as 值
from t a -- 根节点
left join t b on b.parentid=a.id -- 表名节点
left join t c on c.parentid=b.id -- 字段节点
left join t d on d.parentid=c.id -- 值节点
where a.id=0
哦吼，我们只需要处理一下 result_pk，如果有这个字段，就将这个字段的值设置为 scope_identity()，并将@result_pk替换成这个值就可以了，至于怎么插入或更新到表，参考之前的通用插入和更新，这里就不细说了
对于openxml来说，xml文档的结构虽然可以不确定，但实际处理时，总有一个相对确定的框架结构，如果完全没有结构，虽然也不是不能处理，但实际应用的意义就不是那么大了。
