Q:

SQL Server : read XML data

I have this query taken from the site www.SQLauthority.com:
DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

SELECT
    a.b.value('Colors[1]/Color1[1]','varchar(10)') AS Color1,
    a.b.value('Colors[1]/Color2[1]','varchar(10)') AS Color2,
    a.b.value('Colors[1]/Color3[1]','varchar(10)') AS Color3,
    a.b.value('Colors[1]/Color4[1]/@Special','varchar(10)')+' '+
    +a.b.value('Colors[1]/Color4[1]','varchar(10)') AS Color4,
    a.b.value('Colors[1]/Color5[1]','varchar(10)') AS Color5,
    a.b.value('Fruits[1]/Fruits1[1]','varchar(10)') AS Fruits1,
    a.b.value('Fruits[1]/Fruits2[1]','varchar(10)') AS Fruits2,
    a.b.value('Fruits[1]/Fruits3[1]','varchar(10)') AS Fruits3,
    a.b.value('Fruits[1]/Fruits4[1]','varchar(10)') AS Fruits4
FROM @MyXML.nodes('SampleXML') a(b) 

I am not getting a better picture of how the nodes fetching from the xml data.
I have few queries regarding this.

what is a(b) in this?
how the structure will change if i have another node inside colors and all the existing child nodes appended to that?

ie:
 <Colorss>
   <Colors>
      <Color1>White</Color1>
      <Color2>Blue</Color2>
      <Color3>Black</Color3>
      <Color4 Special="Light">Green</Color4>
      <Color5>Red</Color5>
   </Colors>
<Colorss>
<Fruits>
     <Fruits1>Apple</Fruits1>
     <Fruits2>Pineapple</Fruits2>
     <Fruits3>Grapes</Fruits3>
     <Fruits4>Melon</Fruits4>
</Fruits>

what does it mean by a.b.value? When I mouse over it shows a is derived table. Can I check value of the table a?

Any help in this will be appreciated.

A:

what is a(b) in this?

The call to .nodes('SampleXML') is a XQuery function which returns a pseudo table which contains one column of an XML fragment for each of the elements that this XPath expression matches - and the a(b) is the table alias (a) for that column, and b is the name of the column in that pseudo table containing the XML fragments.

what does it mean by a.b.value? 

This is based on the above - a is the table alias for that temporary, inline pseudo table, b is the column name for the column in that table, and .value() is another XQuery function that will extract a single value from XML, based on the XPath expression (first argument) and it will return it as the datatype specified in the second argument.
You should check out those introductions to XQuery support in SQL Server to understand better:

Introduction to XQuery in SQL Server 2005
XQuery basics

and there are numerous other introductions and tutorials on XQuery - just search with your favorite search engine and you'll get tons of hits!

