trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example? I am trying to learn how to turn XML into heirarchical SQL tables.

I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone could help figure out what is going on line by line on this code, particularly after the xml has been loaded onto @XML

--I understand this part, just making the tables  

DECLARE @Books TABLE (BookID int identity(1,1),BookTitle varchar(50),BookLanguage varchar(20),BookPrice decimal(18,2))  
DECLARE @Topics TABLE (TopicID int identity(1,1),BookID int,TopicTitile varchar(50),Page int)  

--I understand this part, defining the @xml variable to be the xml below.. just a usual xml...


DECLARE @xml XML  
SET @xml = '  
&lt;bookstore&gt;  
    &lt;name&gt;My Bookstore&lt;/name&gt;&lt;br/&gt;  
    &lt;location&gt;New York&lt;/location&gt;&lt;br/&gt;  
    &lt;book&gt;  
        &lt;title lang=&amp;quot;eng&amp;quot;&gt;Harry Potter&lt;/title&gt;  
        &lt;price&gt;29.99&lt;/price&gt;  
        &lt;tableOfContents&gt;  
            &lt;topic&gt;  
                &lt;title&gt;Harry Potter Topic 1&lt;/title&gt;  
                &lt;page&gt;2&lt;/page&gt;  
            &lt;/topic&gt;  
            &lt;topic&gt;  
                &lt;title&gt;Harry Potter Topic 2&lt;/title&gt;
                &lt;page&gt;5&lt;/page&gt;
            &lt;/topic&gt;
        &lt;/tableOfContents&gt;
    &lt;/book&gt;
    &lt;book&gt;
        &lt;title lang=&amp;quot;eng&amp;quot;&gt;Learning XML&lt;/title&gt;
        &lt;price&gt;39.95&lt;/price&gt;
        &lt;tableOfContents&gt;
            &lt;topic&gt;
                &lt;title&gt;Learning XML Topic 1&lt;/title&gt;
                &lt;page&gt;1&lt;/page&gt;
            &lt;/topic&gt;
            &lt;topic&gt;
                &lt;title&gt;Learning XML Topic 2&lt;/title&gt;
                &lt;page&gt;2&lt;/page&gt;
            &lt;/topic&gt;
        &lt;/tableOfContents&gt;
    &lt;/book&gt;
&lt;/bookstore&gt;'



--what is going on below here?  I am familiar with inserting data into tables,  
--but what kind of insert is this where you are selecting some things and then doing a  
--from @xml.nodes also, what is that T(c) at the end?  and do we always have to put    
--a [1] after each xml element to denote we are referring to the first one we encounter?  


INSERT INTO @Books  
SELECT T.c.value('title[1]','varchar(50)') AS 'BookTitle',  
    T.c.value('(title/@lang)[1]','varchar(20)') AS 'BookLanguage',  
    T.c.value('price[1]','decimal(18,2)') AS 'BookPrice'  
FROM @xml.nodes('/bookstore/book') T(c)  


--what is going on here as well?  what is n(x) ? 
--could you explain this line by line-ish as well? I ran this on  
--SQL Server Management Studio and noticed that both of the 'topic titles' for each  
--book got inserted.  Where in the code did those get put into the table?

INSERT INTO @Topics  
SELECT b.BookID,n.x.value('title[1]','varchar(50)') AS 'TopicTitile',  
    n.x.value('page[1]','int') AS 'TopicPage'  
FROM @Books b   
cross apply @xml.nodes('/bookstore/book/tableOfContents/topic[../../title=sql:column("b.BookTitle")]') n(x)  


--below here is just regular sql selects so this makes sense.  

SELECT BookID,BookTitle,BookLanguage,BookPrice FROM @Books  
SELECT TopicID,BookID,TopicTitile,Page FROM @Topics  


The forum I was referring to and trying to learn from with the old post is:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/7216ccc9-c1d7-418d-95a2-ec3a96de2c27
BOL says: 

syntax:

nodes (XQuery) as Table(Column)


Here is simple example:

DECLARE @x xml ;
SET @x='&lt;Root&gt;
    &lt;row id="1"&gt;&lt;name&gt;Larry&lt;/name&gt;&lt;oflw&gt;some text&lt;/oflw&gt;&lt;/row&gt;
    &lt;row id="2"&gt;&lt;name&gt;moe&lt;/name&gt;&lt;/row&gt;
    &lt;row id="3" /&gt;
&lt;/Root&gt;';

SELECT T.c.query('.') AS result
FROM   @x.nodes('/Root/row') T(c);
GO


It is a special notician to "convert" or shred xml data type into relational data. It just maps xml parts into the table columns. T - table, c - column, nodes() - method

value (XQuery, SQLType)


So T.c.value('title[1]','varchar(50)') reads value of element title and converts it to varchar(50) data type. [1] is added at the end of the path expression in the value() method to explicitly indicate that the path expression returns a singleton (just it confuses me, it means the first element in group in XPath).

So T.c.value('(title/@lang)[1]','varchar(20)') reads value of attribute lang at element title and converts it into varchar(20) data type. 

And @xml.nodes('/bookstore/book') is located the point to start reading xml, in this case it returns all book elements (nodes) from this xml.

This query has 2 aliases T1(Locations) and T2(steps) 

ADDED

SELECT 
ProductModelID
, Locations.value('./@LocationID','int') as LocID
, steps.query('.') as Step       
FROM Production.ProductModel       

CROSS APPLY Instructions.nodes('       
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
/MI:root/MI:Location') 
as T1(Locations) 

CROSS APPLY T1.Locations.nodes('       
declare namespace MI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";       
./MI:step ') 
as T2(steps)       
GO