SQL Server中的XML数据类型详解_MsSql_

--创建表，包含Xml类型列 　　CREATE TABLE Person 　　( 　　Id int, 　　Info xml 　　)

--插入3条测试数据 　　INSERT Person VALUES(1,'1刘备') 　　INSERT Person VALUES(2,'2关羽') 　　INSERT Person VALUES(3,'3张飞')

insert Person values(4,select * from openrowset(bulk 'G:\Document\XMLDocument\x3.xml',single_clob) as x)

--XML“主”索引 create primary xml index IX_Person_Info on Person ( Info ); --XML“路径”辅助索引 create xml index IX_Person_Info_Path on Person ( Info ) using xml index IX_Person_Info for path; --XML“属性”辅助索引 create xml index IX_Person_Info_Property on Person ( Info ) using xml index IX_Person_Info for property; --XML“内容”辅助索引 create xml index IX_Person_Info_value on Person ( Info ) using xml index IX_Person_Info for value;

--查询节点内容query()方法 SELECT Id,Info.query('(/Person/Name)[1]') FROM Person WHERE ID = 2

declare @myxml xml set @myxml='王五18
湖南李一20
湖北' select @myxml.query(' for $ss in /people/student where $ss/Age[text()]<22 return element Res { (attribute age{data($ss/Age[text()[1]])}) }')

declare @x xml; set @x = ' 彪阿彪流氓光辉二辉流氓小德小D臭流氓'; --1、取root的所有子节点 select @x.query('root'), @x.query('/root'), @x.query('.'); --/*注释： -- 这里实际上是取所有节点,root 必须是最高级节点名称，当换成任意子节点都是取不到值的 --*/ --2、取 student 的所有子节点,不管 student 在文档中的位置。 select @x.query('//student '); --3、取people下 所有 name select @x.query('//people//name'); --4、取属性为id 的所有节点 select @x.query('//student [@id]'); /*注释： XQuery不支持直接顶级 attribute 节点，必须附带上对节点的查找 属性必须要加[] */ --5、选取属于 root 子元素的第一个 people 元素。 select @x.query('/root/people[1]'); --6、选取属于 root 子元素的最后一个 people 元素。 select @x.query('/root/people[last()]'); --7、选取属于 root 子元素的倒数第二个 people 元素。 select @x.query('/root/people[last()-1]'); --8、选取最前面的两个属于 root 元素的子元素的 people 元素。 select @x.query('/root/people[position()<3]'); --9、选取 root 元素的所有 student 元素，且其中的属性 id 的值须大于 1。 select @x.query('/root//student [@id>1]'); ----10、 root 元素的所有 student 元素，且其中的属性 id 的值须大于 1 并且子节点 name 的值为 光辉 的。 select @x.query('/root/people[./student [@id>1 and name="光辉"]]'); --11、选取 root 子元素的所有 people 元素，且 属性id 的值须大于 为001 子元素student 属性 id 的值为 1的 select @x.query('/root/people[@id="001" and ./student [@id=1]]'); --12、if then else 表达式 select @x.query(' if ( 1=2 ) then /root/people[@id="001"] else /root/people[@id="002"] '); --13、路径表达式步骤中的谓词 select @x.query('/root/people[1]/student /name'); --选择第一个 /root/people 节点下的所有  元素。 select @x.query('/root/people/student [1]/name'); --选择 /root/people/student 节点下的所有  元素。 select @x.query('/root/people/student /name[1]'); --选择 /root/people/student 节点下的所有第一个  元素。 select @x.query('(/root/people/student /name)[1]'); --选择 /root/people/student 节点下的第一个  元素。 --14、使用聚合函数 select @x.query('count(/root/people/student /name)'), @x.query('count(/root/people/student /name[1])'); --15、FLWOR 迭代语法。FLWOR 是 for、let、where、order by 和 return 的缩写词。 --1 select @x.query('  { for $i in /root/people/student /name[1] return string($i) } '); --彪 光辉 小德 --2 select @x.query(' for $Loc in /root/people/student , $FirstStep in $Loc/name[1] return string($FirstStep) '); --彪 光辉 小德 --3 select @x.query(' for $i in /root/people/student order by $i/@id descending return string($i/name[1]) '); --小德 光辉 彪 --4 select @x.query(' for $i in /root/people/student order by local-name($i) return string($i/name[1]) '); --彪 光辉 小德

SELECT Id,Info.value('(/Person/Name)[1]','VARCHAR(50)') FROM Person WHERE ID = 2 SELECT * FROM Person WHERE Info.value('(/Person/Name)[1]','VARCHAR(50)') = '张飞'

SELECT * FROM Person WHERE Info.exist('(/Person/Name)[1]') = 1

--1、判断 student 中属性 id 的值 是否为空 select @x.exist('(/root/people/student/@id)[1]'); --2、判断指定节点值是否相等 declare @xml xml = 'a'; select @xml.exist('(/root/name[text()[1]="a"])'); --3、比较日期 --代码 cast as xs:date? 用于将值转换为 xs:date 类型，以进行比较。 --@Somedate 属性的值是非类型化的。比较时，此值将隐式转换为比较右侧的类型（xs:date 类型）。 --可以使用 xs:date() 构造函数，而不用 cast as xs:date()。 declare @a xml; set @a = ''; select @a.exist('/root[(@Somedate cast as xs:date?) eq xs:date("2012-01-01")]');

--查询节点 　　SELECT T2.Loc.query('.') as result 　　FROM Person 　　CROSS APPLY Info.nodes('/Person/Name') as T2(Loc)

--获得所有student节点的数据,每一行显示一条student节点的数据 select T.c.query('.') as result from @myxml.nodes('/people/student') as T(c) --将这些数据显示为一个表格 select T.c.value('(@id)[1]','int') as id, T.c.value('(./Name)[1]','nvarchar(16)') as name, T.c.value('(./Age)[1]','int') as age, T.c.value('(./Address)[1]','nvarchar(16)') as address from @myxml.nodes('/people/student') as T(c)

--1、 对表中的 xml 数据进行解析, 节点下面有多个相同节点的 使用 cross apply 和 nodes() 方法解析 if object_id('tempdb..[#tb]') is not null drop table [#tb]; create table [#tb] ( [id] int , [name] xml ); insert [#tb] select 1, 'ab' union all select 2, 'b' union all select 3, 'd'; select id, T.c.query('.'), T.c.value('.', 'sysname') from [#tb] A cross apply A.name.nodes('/r/i') T(c); --2、利用xml 拆分字符串 declare @s varchar(100) = '1,2,3,4,5,6'; select T.c.value('.', 'int') as col from ( select cast('' + replace(@s, ',', '') + '' as xml).query('.') as name ) as a cross apply a.name.nodes('/x') T(c); --3、取任意属性的属性值，这里引入了 sql:variable declare @x1 xml; select @x1 = ' '; declare @pos int; select @pos = 2; select @x1.value('local-name( (/Employees/Employee[2]/@*[position()=sql:variable("@pos")])[1] )', 'VARCHAR(20)') as AttName; --4、将普通数据列和 xml 数据列进行合并 --sql:column() 函数 declare @t1 table ( id int , data xml ); insert into @t1 ( id, data ) select 1, '二辉流氓' union all select 2, '彪流氓'; select id, data = data.query('{sql:column("id")}提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                

                

                

                                            
上一篇：SQL实现查询某字段的值为空的记录_MsSql_
                                                                
下一篇：SQL Server的全文搜索功能_MsSql_