trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?
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