試圖將 XML 轉換為層次結構的 SQL 表，任何人都可以很好地解釋這個程式碼範例的兩個部分嗎？

我正在嘗試學習如何將 XML 轉換為層次結構的 SQL 表。

我從微軟論壇中找到了一個舊程式碼片段，這基本上是我想要做的，但我想知道是否有人可以幫助弄清楚這段程式碼的逐行情況，特別是在將 xml 載入到@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 [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  

我所指並試圖從舊文章中學習的論壇是：

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/7216ccc9-c1d7-418d-95a2-ec3a96de2c27

波爾 說：

句法：

nodes (XQuery) as Table(Column)

這是一個簡單的例子：

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

將 xml 數據類型“轉換”或分解為關係數據是一種特殊的通知。它只是將 xml 部分映射到表列中。T - 表，c - 列，nodes() - 方法

value (XQuery, SQLType)

所以 Tcvalue(’title

$$ 1 $$’,‘varchar(50)’) 讀取元素標題的值並將其轉換為 varchar(50) 數據類型。$$ 1 $$在 value() 方法中的路徑表達式末尾添加，以明確指示路徑表達式返回單例（只是讓我感到困惑，它表示 XPath 中 group 中的第一個元素）。 所以 Tcvalue(’(title/@lang)

$$ 1 $$’,‘varchar(20)’) 在元素標題處讀取屬性 lang 的值並將其轉換為 varchar(20) 數據類型。 @xml.nodes(’/bookstore/book’) 位於開始讀取 xml 的位置，在這種情況下，它會從該 xml 返回所有書籍元素（節點）。

此查詢有 2 個別名 T1（位置）和 T2（步驟）

添加

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       

引用自：https://dba.stackexchange.com/questions/1419