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 = '

My Bookstore
New York

Harry Potter
29.99


Harry Potter Topic 1
2


Harry Potter Topic 2
5




Learning XML
39.95


Learning XML Topic 1
1


Learning XML Topic 2
2



'

--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 bo

BOL says:

syntax:

nodes (XQuery) as Table(Column)

Here is simple example:

DECLARE @x xml ;
SET @x='
    Larrysome text
    moe
    
';

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

StackExchange Database Administrators Q#1419, answer score: 4