CODEWITHSUNDEEP
sql-serverxmlsql-server-2008sql-server-2012
Tom Cruise
Tom Cruise

Reputation: 1425

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.

  1. what is a(b) in this?

  2. 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>
  1. 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.

Upvotes: 1

Views: 348

Answers (2)

marc_s
marc_s

Reputation: 755217

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:

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

Upvotes: 2

Napstar
Napstar

Reputation: 363

here's my stab @ it:

  1. a-refers to root;b-refers to root and child node
    DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Colors>
<Color1>White</Color1>
<Color2>Blue</Color2>
<Color3>Black</Color3>
<Color4 Special="Light">Green</Color4>
<Color5>Red
  <Color6>Black44</Color6>
  <Color7>Black445</Color7>
</Color5>
</Colors>
<Fruits>
<Fruits1>Apple</Fruits1>
<Fruits2>Pineapple</Fruits2>
<Fruits3>Grapes</Fruits3>
<Fruits4>Melon</Fruits4>
</Fruits>
</SampleXML>'

  1. to get an inner child

    SELECT a.c.value('Colors1/Color11','varchar(10)') AS Color1, a.c.value('Colors1/Color21','varchar(10)') AS Color2, a.c.value('Colors1/Color31','varchar(10)') AS Color3, a.c.value('Colors1/Color41/@Special','varchar(10)') AS Color4, a.c.value('Colors1/Color51','varchar(10)') AS Color5, a.c.value('Colors1/Color51/Color71','varchar(50)') AS Color6a, a.c.value('Colors1/Color51/Color61','varchar(50)') AS Color6b, a.c.value('Fruits1/Fruits11','varchar(10)') AS Fruits1, a.c.value('Fruits1/Fruits21','varchar(10)') AS Fruits2, a.c.value('Fruits1/Fruits31','varchar(10)') AS Fruits3, a.c.value('Fruits1/Fruits41','varchar(10)') AS Fruits4 FROM @MyXML.nodes('SampleXML') a(c)

  2. A nodes() method invocation with the query expression /root/Color(n) would return a rowset with three rows, each containing a logical copy of the original XML document, and with the context item set to one of the nodes see here

Upvotes: 1

Related Questions