代码之家 › 专栏 › 技术社区 › Ross Bush

通过重复表id将重复的xml节点值从xml列转换为#表

xml sql-server

Ross Bush · 技术社区 · 6 年前

对于表结构,如下所示:

LibraryID(INT)  XMLData(NVARCHAR(MAX))
-----------     --------------------
1               <Library xmlns:xsi="http:...
2               <Library xmlns:xsi="http:...
3               <Library xmlns:xsi="http:...

TableID=1的XMLData包含如下值:

<Library xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Books>
        <LibraryBook>
            <Author>Author 1</Author>
            <Title>Title 1</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 2</Author>
            <Title>Title 2</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 3</Author>
            <Title>Title 3</Title>
        </LibraryBook>
    </Books>
    <Magazines>
    ...
    </Magazines>
</Library>

我希望输出为:

LibraryID(INT)  Author      Title
-----------     ---------   -------
1               Author 1    Title 1
1               Author 2    Title 2
1               Author 3    Title 3
2               ...         ...
3               ...         ...  
3               ...         ...

我尝试了以下问题:

;WITH XmlData AS
(
    SELECT
        LibraryID,
        XmlNodes = CAST(XmlData AS XML)
    FROM
        Library
)
,BrokenDown AS
(
    SELECT 
        LibraryID, 
        Author = XmlNodes.value('(/Library/Books/LibraryBook/Author)[1]', 'VARCHAR(100)'),
        Title = XmlNodes.value('(/Library/Books/LibraryBook/Title)[1]', 'VARCHAR(100)')
    FROM 
        XmlData
)

SELECT * FROM BrokenDown

输出仅列出每个LibraryID的第一本书和标题:

LibraryID    Author      Title
---------    ------      -----
1            Author 1    Title 1
2            ...         ...
3            ...         ...

任何帮助都将不胜感激。

2 回复 | 直到 6 年前

marc_s MisterSmith 6 年前

一旦“清理”了XML(结束标记 </FieldName> 和 </DisplayIndex> 与开始标签不匹配 <Author> 和 <Title >…)-一旦你把你的专栏定义为 XML (既然它只包含XML,为什么不声明为 XML 首先???-你可以试试这个:

SELECT
    LibraryID,
    Author = XC.value('(Author/text())[1]', 'varchar(50)'),
    Title = XC.value('(Title/text())[1]', 'varchar(50)')
FROM
    dbo.Library
CROSS APPLY
    XmlData.nodes('/Library/Books/LibraryBook') AS XT(XC)

如果你必须保留那些不是很有用的东西 NVARCHAR(MAX) 数据类型——然后,您需要在开始之前进行“转换为XML”CTE SELECT 这样地:

;WITH XmlCte AS 
(
    SELECT 
        LibraryId, 
        RealXmlData = CAST(XmlData AS XML)
    FROM dbo.Library
)
SELECT
    LibraryId,
    Author = XC.value('(Author/text())[1]', 'varchar(50)'),
    Title = XC.value('(Title/text())[1]', 'varchar(50)')
FROM
    RealXmlData
CROSS APPLY
    XmlData.nodes('/Library/Books/LibraryBook') AS XT(XC)

更新: 补充道 /text() XQuery中选择的两个XML元素的表达式——感谢@YitzhakKhabinsky的提示——大大加快了查询速度!

David Browne - Microsoft 6 年前

比如:

use tempdb
go
drop table if exists Library
go

create table Library(LibraryId int identity primary key, XmlData xml)

go

insert into Library(XmlData)
values 
('<Library xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Books>
        <LibraryBook>
            <Author>Author 1</Author>
            <Title>Title 1</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 2</Author>
            <Title>Title 2</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 3</Author>
            <Title>Title 3</Title>
        </LibraryBook>
    </Books>
    <Magazines>
    ...
    </Magazines>
</Library>  '
),
('<Library xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Books>
        <LibraryBook>
            <Author>Author 1</Author>
            <Title>Title 1</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 2</Author>
            <Title>Title 2</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 3</Author>
            <Title>Title 3</Title>
        </LibraryBook>
    </Books>
    <Magazines>
    ...
    </Magazines>
</Library>  '
),
('<Library xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <Books>
        <LibraryBook>
            <Author>Author 1</Author>
            <Title>Title 1</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 2</Author>
            <Title>Title 2</Title>
        </LibraryBook>
        <LibraryBook>
            <Author>Author 3</Author>
            <Title>Title 3</Title>
        </LibraryBook>
    </Books>
    <Magazines>
    ...
    </Magazines>
</Library>  '
)

select l.LibraryId, 
       book.value('(Author)[1]','varchar(20)') Author, 
       book.value('(Title)[1]','varchar(20)') Title 
from Library l
outer apply l.XmlData.nodes('Library/Books/LibraryBook') books(book)

输出

LibraryId   Author               Title
----------- -------------------- --------------------
1           Author 1             Title 1
1           Author 2             Title 2
1           Author 3             Title 3
2           Author 1             Title 1
2           Author 2             Title 2
2           Author 3             Title 3
3           Author 1             Title 1
3           Author 2             Title 2
3           Author 3             Title 3

(9 rows affected)