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

SET @xml = '  
    <name>My Bookstore</name><br/>  
    <location>New York</location><br/>  
        <title lang=&quot;eng&quot;>Harry Potter</title>  
                <title>Harry Potter Topic 1</title>  
                <title>Harry Potter Topic 2</title>
        <title lang=&quot;eng&quot;>Learning XML</title>
                <title>Learning XML Topic 1</title>
                <title>Learning XML Topic 2</title>

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

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?

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:



BOL says:


nodes (XQuery) as Table(Column)

Here is simple example:

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

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

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)


, 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";       
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)       
  • Thank you. I think I understand the INSERT INTO @Books section now. and thanks for the extra simple example. In my original question code, under INSERT INTO @Topics, can you give me an example of how and when to use cross apply? Also, syntax-wise, what does the x. mean? for example in n.x.value, and n(x)?
    – fa1c0n3r
    Feb 24 '11 at 10:19
  • @fa1c0n3r it's just alias n - table, x - column, see my edited answer. In your case, you can use x.value..(without n) in select clause, because of one table.
    – garik
    Feb 24 '11 at 10:52
  • 1
    thanks do you happen to have a list somewhere of all the default reserved aliases in sql 2005 or 2008? n and x seem to be already used and reserved in the system right? Regarding cross apply, I found another cross apply example on the web which explains it pretty quickly: thesqlagentman.com/2010/01/cross-apply
    – fa1c0n3r
    Feb 25 '11 at 8:33
  • 1
    Another Cross Apply example I found on the web: sqlteam.com/article/using-cross-apply-in-sql-server-2005
    – fa1c0n3r
    Feb 28 '11 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.