XML格式是
<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> </bookstore> 想要获取第2个book的author值
现在使用的方式是
select @b = Tab.Col.value('(book/author)[2]','varchar(max)')
from @data.nodes('bookstore')as Tab(Col) ;这种方式可以正常获取现在我需要获取第N个book的author值,定义了@i = 1
select @b = Tab.Col.value('(book/author)['+@i+‘]','varchar(max)')
from @data.nodes('bookstore')as Tab(Col) ;
报错了。。该怎么办
怎么获取某个不确定节点的某个值?
<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> </bookstore> 想要获取第2个book的author值
现在使用的方式是
select @b = Tab.Col.value('(book/author)[2]','varchar(max)')
from @data.nodes('bookstore')as Tab(Col) ;这种方式可以正常获取现在我需要获取第N个book的author值,定义了@i = 1
select @b = Tab.Col.value('(book/author)['+@i+‘]','varchar(max)')
from @data.nodes('bookstore')as Tab(Col) ;
报错了。。该怎么办
怎么获取某个不确定节点的某个值?
<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> </bookstore> 'DECLARE @b VARCHAR(100)
,@i INT=2
SELECT @[email protected]('(bookstore/book[position()=sql:variable("@i")]/author)').value('.','varchar(100)')
SELECT @b
/*
J K. Rowling
*/