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="eng">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="eng">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  after each xml element to denote we are referring to the first one we encounter? INSERT INTO @Books SELECT T.c.value('title','varchar(50)') AS 'BookTitle', T.c.value('(title/@lang)','varchar(20)') AS 'BookLanguage', T.c.value('price','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','varchar(50)') AS 'TopicTitile', n.x.value('page','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: