Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 = '  
<bookstore>  
    <name>My Bookstore</name><br/>  
    <location>New York</location><br/>  
    <book>  
        <title lang=&quot;eng&quot;>Harry Potter</title>  
        <price>29.99</price>  
        <tableOfContents>  
            <topic>  
                <title>Harry Potter Topic 1</title>  
                <page>2</page>  
            </topic>  
            <topic>  
                <title>Harry Potter Topic 2</title>
                <page>5</page>
            </topic>
        </tableOfContents>
    </book>
    <book>
        <title lang=&quot;eng&quot;>Learning XML</title>
        <price>39.95</price>
        <tableOfContents>
            <topic>
                <title>Learning XML Topic 1</title>
                <page>1</page>
            </topic>
            <topic>
                <title>Learning XML Topic 2</title>
                <page>2</page>
            </topic>
        </tableOfContents>
    </book>
</bookstore>'



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

Thanks a lot!

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

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

BOL says:

syntax:

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" />
</Root>';

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       
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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