22,207
社区成员
发帖
与我相关
我的任务
分享
--1、文档
select @ data
--2、任意级别是否存在price节点
select @ data . exist ('//price' )
--3、获取所有book节点
select @ data . query ('//book' )
--4、获取所有包含lang属性的节点
select @ data . query ('//*[@lang]' )
--5、获取第一个book节点
select @ data . query ('//book[1]' )
--6、获取前两个book节点
select @ data . query ('//book[position()<=2]' )
--7、获取最后一个book节点
select @ data . query ('//book[last()]' )
--8、获取price>35的所有book节点
select @ data . query ('//book[price>35]' )
--9、获取category="WEB"的所有book节点
select @ data . query ('//book[@category="WEB"]' )
--10、获取title的lang="en"的所有book节点
select @ data . query ('//book/title[@lang="en"]' )
--11、获取title的lang="en"且 price>35的所有book节点
select @ data . query ('//book[./title[@lang="en"] or price>35 ]' )
--12、获取title的lang="en"且 price>35的第一book的(第一个)title
select @ data . query ('//book[./title[@lang="en"] and price>35 ]' ). value ('(book/title)[1]' , 'varchar(max)' )
--13、等价于10
select @ data . value ('(//book[./title[@lang="en"] and price>35 ]/title)[1]' , 'varchar(max)' )
--14、获取title的lang="en"且 price>35的第一book的(第一个)title的lang属性
select @ data . value ('((//book[@category="WEB" and price>35 ]/title)[1]/@lang)[1]' , 'varchar(max)' )
--15、获取第一本书的title
select Tab . Col . value ('(book/title)[1]' , 'varchar(max)' ) as title
from @ data . nodes ('bookstore' )as Tab (Col )
--16、获取每本书的第一个author
select Tab . Col . value ('author[1]' , 'varchar(max)' ) as title
from @ data . nodes ('//book' )as Tab (Col )
--17、获取所有book的所有信息
select
T . C . value ('title[1]' , 'varchar(max)' ) as title ,
T . C . value ('year[1]' , 'int' ) as year ,
T . C . value ('title[1]' , 'varchar(max)' )as title ,
T . C . value ('price[1]' , 'float' ) as price ,
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
from @ data . nodes ('//book' ) as T (C )
--18、获取不是日语(lang!="jp")且价格大于35的书的所有信息
select
T . C . value ('title[1]' , 'varchar(max)' ) as title ,
T . C . value ('year[1]' , 'int' ) as year ,
T . C . value ('title[1]' , 'varchar(max)' )as title ,
T . C . value ('price[1]' , 'float' ) as price ,
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
from @ data . nodes ('//book[./title[@lang!="jp"] and price>35 ]' ) as T (C )
DECLARE @x XML
SET @x='
<MasterxRow>
<ActivityType>2</ActivityType>
<ProductType>21</ProductType>
<RefNo>222222222222222222222</RefNo>
<ContactResult>0</ContactResult>
</MasterxRow>'
IF @x.exist('//ProductType[.=21]')=1
PRINT 'aa'
ELSE
PRINT 'bb'