主页 / dba / 问题 / 1419
Accepted
fa1c0n3r
Asked: 2011-02-24 21:46:58 +0800 CST

试图将 XML 转换为层次结构的 SQL 表，任何人都可以很好地解释这个代码示例的两个部分吗？

  • 772

我正在尝试学习如何将 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=&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

我所指并试图从旧帖子中学习的论坛是：

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

sql-server-2008 sql-server-2005

1 个回答

  1. Best Answer

    波尔 说：

    句法：

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

相关问题