Where can I find information on shredding xml with .nodes? I recently saw this answer on SO which uses the "local-name" value while shredding xml using .nodes(). I've never seen this before:

select T.N.value('local-name(.)', 'varchar(128)') as Name,
       T.N.value('.', 'varchar(max)') as Value
from @xml.nodes('/data/*') as T(N);


Where can I find the definitive document on commands like .nodes() and .value()?
Those methods are part of the xml data type in Sql Server, starting from 2005, since the xml data type was implemented for Sql Server 2005.

The local-name method is part of XQuery implementation of Sql Server. Its far from being a complete implementation of the XQuery specification, but it can be helpful.