Question about VALUE

Question

Question about VALUE

djj

SSCoach

Points: 18831
More actions
November 24, 2014 at 10:55 am

#298840

In the thread: http://www.sqlservercentral.com/Forums/Topic1637167-392-5.aspx Lynn Pettis, showed the following code (I deleted a line or two):
create table dbo.Tbl_ServerList(
server_name sysname
);
insert into dbo.Tbl_ServerList
values ('MyServer1'),('MyServer2');
go
declare @SQLCmd nvarchar(max);
select @SQLCmd = stuff((select N'union all select Loginame,HostName,DbName,cmd from openrowset(''sqlncli'',''server='''
+ server_name + ';Trusted_Connection=yes;'',''exec sp_who'')' + nchar(13) + nchar(10)
from dbo.Tbl_ServerList
order by server_name
for xml path(''),TYPE).value('.','nvarchar(max)'),1,10,'');
print @SQLCmd;
go
drop table dbo.Tbl_ServerList;
go
I understand how most of it works until I get to the .value('.', I do not understand how the "." can be an XQuery as I do not know what an XQuery is (I know it get information from xml).
So my question is how does the "." work?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I recommend you read the series of posts on XQuery by Jason Strate found here, http://www.jasonstrate.com/tag/xquery/. He does a better job explaining it than I can, but, briefly, .value will return the value found in the current node of an XML document.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

djj SSCoach Points: 18831 More actions · Answer 2

Thanks for the link. That is a lot of information.:-)

-- Found my answer in the link: "The period is used to designate that the element value to return is at the level that the nodes() method has browsed to."

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

Quick note (for completeness), the inner query returns an XML, the value() method is invoked on that XML

😎

(inner query returning XML).value() <--the value method on the XML data type.

djj SSCoach Points: 18831 More actions · Answer 4

Eirikur Eiriksson (11/24/2014)
Quick note (for completeness), the inner query returns an XML, the value() method is invoked on that XML
😎
(inner query returning XML).value() <--the value method on the XML data type.

For anyone who does not understand what is happening is the STUFF(..., 1, 10) removes the UNION from the beginning of the XML code returned by the VALUE.