首页 > 数据库 >sqlserver xml 操作:1、使用for xml

sqlserver xml 操作:1、使用for xml

时间:2022-11-14 20:36:18浏览次数:72  
标签:xml NULL name sqlserver 操作 null select cate

sqlserver xml 操作:1、使用for xml

一个是侧重介绍for xml模式的,一个是侧重语法及细节参数的

for xml (Sql Server)

for xml 子句的基本语法

说实话,老顾觉得其实看看这两个文章基本就能满足学习的需要了,毕竟里面也有不少示例了,不过有qq群的同学想问些基本概念、语法、用法、数据库里面的应用等等,那老顾就来水几篇文章好了

[ FOR { BROWSE | <XML> } ]  
<XML> ::=  
XML   
    {   
      { RAW [ ('ElementName') ] | AUTO }   
        [   
           <CommonDirectives>   
           [ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
           [ , ELEMENTS [ XSINIL | ABSENT ]   
        ]  
      | EXPLICIT   
        [   
           <CommonDirectives>   
           [ , XMLDATA ]   
        ]  
      | PATH [ ('ElementName') ]   
        [   
           <CommonDirectives>   
           [ , ELEMENTS [ XSINIL | ABSENT ] ]  
        ]  
     }   
  
 <CommonDirectives> ::=   
   [ , BINARY BASE64 ]  
   [ , TYPE ]  
   [ , ROOT [ ('RootName') ] ]
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

for xml 就是基本的sql查询语句后追加的一个输出方式的限定

这里以master…spt_values表的数据作为依据来进行展示

先来个正常的select

select * from master..spt_values

------------------------------------------
name                                number      type low         high        status
----------------------------------- ----------- ---- ----------- ----------- -----------
rpc                                 1           A    NULL        NULL        0
pub                                 2           A    NULL        NULL        0
sub                                 4           A    NULL        NULL        0
dist                                8           A    NULL        NULL        0
dpub                                16          A    NULL        NULL        0
rpc out                             64          A    NULL        NULL        0
data access                         128         A    NULL        NULL        0
......
backup device                       16          V    NULL        NULL        0
serial writes                       32          V    NULL        NULL        0
read only                           4096        V    0           1           0
deferred                            8192        V    0           1           0

(2552 行受影响)
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20

然后来个追加了for xml的语句,因为有四种模式,各自来一遍

select * from master..spt_values for xml raw
 
  • 1

在这里插入图片描述

select * from master..spt_values for xml auto
 
  • 1

在这里插入图片描述

-- path不指定节点名
select * from master..spt_values for xml path('')
 
  • 1
  • 2

在这里插入图片描述

-- path 指定节点名
select * from master..spt_values for xml path('row')
 
  • 1
  • 2

在这里插入图片描述
关于explicit模式,是更灵活的模式,不过相对来说也复杂很多,并且有格式要求,参考https://docs.microsoft.com/zh-cn/sql/relational-databases/xml/use-explicit-mode-with-for-xml?view=sql-server-ver15

个人感觉虽然这个模式下,灵活性确实高出不少,但相对来说,指令也复杂不少,通常情况下,生成多层xml的话,老顾是不会这么写的,有兴趣的同学可以参考https://blog.csdn.net/Beirut/article/details/8163236

大概意思呢,就是自行定义好层深,然后通过嵌套方式来得到一个序列化的xml结果,例如

select b.* 
from (
	select 1 tag,null parent,type [node!1!name],null [value!2!number] ,null [value!2!name]
	from (
		select distinct type 
		from master..spt_values
	) a
) a
cross apply (
	select a.*
	union all
	select 2,1,null,number,name
	from master..spt_values
	where type=a.[node!1!name]
) b
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

在这里插入图片描述


select b.* 
from (
	select 1 tag,null parent,type [node!1!name],null [value!2!number] ,null [value!2!name]
	from (
		select distinct type 
		from master..spt_values
	) a
) a
cross apply (
	select a.*
	union all
	select 2,1,null,number,name
	from master..spt_values
	where type=a.[node!1!name]
) b
for xml explicit,root('spt_values')
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

在这里插入图片描述
也就是说,这个东西对不确定的东西支持不太好,比如无限级分类,我可能有三级,也可以能有6级,但是实现起来就完全不同,需要通过对应数量的cross来实现确定级数的xml,这里用老顾的无限级分类来做个示范


select lv6.* 
from (
	-- 定义顶级分类,tag固定为1,父类固定为null
	select 1 tag,null parent,cate_id [lv1!1!id],cate_name [lv1!1!name]
		-- 预留出2级之后的字段
		,null [lv2!2!id],null [lv2!2!name]
		,null [lv3!3!id],null [lv3!3!name]
		,null [lv4!4!id],null [lv4!4!name]
		,null [lv5!5!id],null [lv5!5!name]
		,null [lv6!6!id],null [lv6!6!name]
	from v_categories with (nolock) 
	where cate_parent=0
) lv1
cross apply (
	select lv1.*
	union all
	select 2,1,null,null,cate_id,cate_name,null,null,null,null,null,null,null,null
	from v_categories with (nolock)
	where cate_parent=lv1.[lv1!1!id]
) lv2
cross apply (
	select lv2.*
	union all
	select 3,2,null,null,null,null,cate_id,cate_name,null,null,null,null,null,null
	from v_categories with (nolock)
	where cate_parent=lv2.[lv2!2!id]
) lv3
cross apply (
	select lv3.*
	union all
	select 4,3,null,null,null,null,null,null,cate_id,cate_name,null,null,null,null
	from v_categories with (nolock)
	where cate_parent=lv3.[lv3!3!id]
) lv4
cross apply (
	select lv4.*
	union all
	select 5,4,null,null,null,null,null,null,null,null,cate_id,cate_name,null,null
	from v_categories with (nolock)
	where cate_parent=lv4.[lv4!4!id]
) lv5
cross apply (
	select lv5.*
	union all
	select 6,5,null,null,null,null,null,null,null,null,null,null,cate_id,cate_name
	from v_categories with (nolock)
	where cate_parent=lv5.[lv5!5!id]
) lv6
for xml explicit,root('产品分类')
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50

在这里插入图片描述
这语句写下来就突出一个离谱。。。。总之呢,就是层级要预先定义,所有用到的字段需要提前按照[节点名!层级!属性名]方式预先定义,然后各种子查询

好了,四种查询模式我们已经都看过效果了,然后是各个参数的使用,我们在之后的使用中再一一介绍

标签:xml,NULL,name,sqlserver,操作,null,select,cate
From: https://www.cnblogs.com/yclizq/p/16890296.html

相关文章

  • sqlserver xml 操作:2、对确定结构的xml进行操作
    sqlserverxml操作:2、对确定结构的xml进行操作我们使用的xml有着明确的定义结构,该啥节点,该啥属性,该啥层级,基本在设计之初就已经确定了,很少有确定了结构的xml格式再......
  • sqlserver xml 操作:3、对不确定结构、属性的xml进行处理
    sqlserverxml操作:3、对不确定结构、属性的xml进行处理在平时使用过程中,xml的结构一般都是已经确定了的,所以我们读取并不是很麻烦的事,但如果一旦结构或数据不确定,......
  • sqlserver FOR XML查询参数path的实例
     SQLSERVER中XML查询:FORXML指定PATH前言在SQLSERVER中,XML查询可以指定RAW,AUTO,EXPLICIT,PATH。本文用一些实例介绍SQLSERVER中指定PATH的XML查询。PA......
  • SQL中的OpenXML使用案例
    DECLARE@idocintDECLARE@docvarchar(1000)SET@doc='<ROOT><CustomerCustomerID="VINET"ContactName="PaulHenriot"><OrderOrderID="10248"Customer......
  • SQL 存储过程 解析XML
    第一种说明:我看过这样一篇文章,如下 在SQL Server2005中,微软延续了 2000中一个特性(即支持XML类型的数据),并加强了对XML 数据列、XML变量以及XML索引的支持。 ......
  • SQL openxml用法及案例
    SQLopenxml用法 OPENXML通过XML文档提供行集视图。由于OPENXML是行集提供程序,因此可在会出现行集提供程序(如表、视图或OPENROWSET函数)的Tra......
  • 把XML数据插入到SQL Server数据库的表中
     把XML数据插入到SQLServer数据库的表中我们可以通过使用系统存储过程sp_xml_preparedocument的OPENXML函数把XML文档中的数据插入数据库中。其中,系统存储过程sp_......
  • IDEA开发工具:字体,快捷键,其他操作
    IDEA开发工具:字体,快捷键,其他操作1.常用快捷键​ main/psvm、sout、...快速键入相关代码​ Ctrl+D复制当前行数据到下一行​ Ctrl+Y删除所在行建议用CTRL+X​ Ctrl......
  • 【JDBC】使用PreparedStatement操作数据库
    1.对数据库调用的不同方式java.sql包下有3个接口Statement:用于执行静态SQL语句。PreparedStatement:SQL语句被预编译并存储在此对象中。CallableStatement:用于执行SQL......
  • 319场周赛 逐层排序二叉树需要的最小操作数目
    319场周赛逐层排序二叉树所需的最小操作数目给你一个值互不相同的二叉树的根节点root。在一步操作中,你可以选择同一层上任意两个节点,交换这两个节点的值。返回每......