SQL XML入门--将Jinjazz大大的18句话放到procedure中， 方便随时查看

/* --本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/jinjazz/archive/2009/08/13/4443585.aspx sql xml 入门: --by jinjazz --http://blog.youkuaiyun.com/jinjazz 1、xml: 能认识元素、属性和值 2、xpath: 寻址语言，类似windows目录的查找(没用过dir命令的话就去面壁) 语法格式，这些语法可以组合为条件： "."表示自己，".."表示父亲，"/"表示儿子，"//"表示后代， "name"表示按名字查找，"@name"表示按属性查找 "集合[条件]" 表示根据条件取集合的子集，条件可以是 数 值：数字,last(),last()-数字 等 布尔值：position()<数字,@name='条件',name='条件' 条件是布尔值的时候可以合并计算：and or 3、xquery: 基于xpath标的准查询语言，sqlserver xquery包含如下函数 exist(xpath条件)：返回布尔值表示节点是否存在 query(xpath条件)：返回由符合条件的节点组成的新的xml文档 value(xpath条件,数据类型)：返回指定的标量值，xpath条件结果必须唯一 nodes(xpath条件): 返回由符合条件的节点组成的一行一列的结果表 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XML_TABLE]') AND type in (N'U')) DROP TABLE [dbo].[XML_TABLE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XML_TABLE]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[XML_TABLE]( [SeqID] [int] IDENTITY(1,1) NOT NULL, [xmlData] [xml] NULL ) ON [PRIMARY] END GO SET IDENTITY_INSERT [dbo].[XML_TABLE] ON INSERT [dbo].[XML_TABLE] ([SeqID], [xmlData]) VALUES (1, N'<bookstore><book category="COOKING"><title lang="en">Everyday Italian</title><author>Giada De Laurentiis</author><year>2005</year><price>30.00</price></book><book category="CHILDREN"><title lang="jp">Harry Potter</title><author>J K. Rowling</author><year>2005</year><price>29.99</price></book><book category="WEB"><title lang="en">XQuery Kick Start</title><author>James McGovern</author><author>Per Bothner</author><author>Kurt Cagle</author><author>James Linn</author><author>Vaidyanathan Nagarajan</author><year>2003</year><price>49.99</price></book><book category="WEB"><title lang="cn">Learning XML</title><author>Erik T. Ray</author><year>2003</year><price>39.95</price></book></bookstore>') SET IDENTITY_INSERT [dbo].[XML_TABLE] OFF exec [uspSqlxml_Learn] 18 */ ALTER procedure [dbo].[uspSqlxml_Learn] ( @Type int=1 ) as declare @xmlData xml select @xmlData =xmldata from xml_table --测试语句，如果不理解语法请参考上面的xpath规则和xquery函数说明 --1、文档 if @Type =1 select @xmlData --2、任意级别是否存在price节点 else if @Type =2 select @xmlData.exist ('//price' ) --3、获取所有book节点 else if @Type =3 select @xmlData.query ('//book' ) --4、获取所有包含lang属性的节点 else if @Type =4 select @xmlData.query ('//*[@lang]' ) --5、获取第一个book节点 else if @Type =5 select @xmlData.query ('//book[1]' ) --6、获取前两个book节点 else if @Type =6 select @xmlData.query ('//book[position()<=2]' ) --7、获取最后一个book节点 else if @Type =7 select @xmlData.query ('//book[last()]' ) --8、获取price>35的所有book节点 else if @Type =8 select @xmlData.query ('//book[price>35]' ) --9、获取category="WEB"的所有book节点 else if @Type =9 select @xmlData.query ('//book[@category="WEB"]' ) --10、获取title的lang="en"的所有book节点 else if @Type =10 select @xmlData.query ('//book[./title[@lang="en"]]' ) --11、获取title的lang="en"且 price>35的所有book节点 else if @Type =11 select @xmlData.query ('//book[./title[@lang="en"] and price>35 ]' ) --12、获取title的lang="en"且 price>35的第一book的(第一个)title else if @Type =12 select @xmlData.query ('//book[./title[@lang="en"] and price>35 ]' ). value ('(book/title)[1]' , 'varchar(max)' ) --13、等价于12 else if @Type =13 select @xmlData.value ('(//book[./title[@lang="en"] and price>35 ]/title)[1]' , 'varchar(max)' ) --14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性 else if @Type =14 select @xmlData.value ('((//book[./title[@lang="en"] and price>35 ]/title)[1]/@lang)[1]' , 'varchar(max)' ) --15、获取第一本书的title else if @Type =15 select Tab.Col.value ('(book/title)[1]' , 'varchar(max)' ) as title from @xmlData.nodes ('bookstore' )as Tab(Col) --16、获取每本书的第一个author else if @Type =16 select Tab.Col.value ('author[1]' , 'varchar(max)' ) as title from @xmlData.nodes ('//book' )as Tab (Col ) --17、获取所有book的所有信息 else if @Type =17 select T.C.value ('./@category[1]' , 'varchar(max)' ) as category , T.C.value ('title[1]' , 'varchar(max)' )as title , --T.C.value ('./title/@lang[1]' , 'varchar(max)' )as Lang , T.C.value ('author[1]' , 'varchar(max)' ) as author1 , T.C.value ('author[2]' , 'varchar(max)' ) as author2 , T.C.value ('author[3]' , 'varchar(max)' ) as author3 , T.C.value ('author[4]' , 'varchar(max)' ) as author4 , T.C.value ('year[1]' , 'int' ) as year , T.C.value ('price[1]' , 'float' ) as price from @xmlData.nodes ('//book' ) as T (C ) --18、获取不是日语(lang!="jp")且价格大于35的书的所有信息 else if @Type =18 select T.C.value ('./@category' , 'varchar(max)' ) as category , T.C.value ('title[1]' , 'varchar(max)' )as title , --T.C.value ('./title/@lang[1]' , 'varchar(max)' )as Lang , T.C.value ('author[1]' , 'varchar(max)' ) as author1 , T.C.value ('author[2]' , 'varchar(max)' ) as author2 , T.C.value ('author[3]' , 'varchar(max)' ) as author3 , T.C.value ('author[4]' , 'varchar(max)' ) as author4 , T.C.value ('year[1]' , 'int' ) as year , T.C.value ('price[1]' , 'float' ) as price from @xmlData.nodes ('//book[./title[@lang!="jp"] and price>35 ]' ) as T (C )