在 SQL Server 中将 XML 节点转换为行 [英] Convert XML Nodes To Rows in SQL Server

问题描述

考虑以下示例:

declare @somexml as xml

set @somexml = '
<Settings>
    <Users>
        <ID>1</ID>
        <ID>2</ID>
        <ID>3</ID>
        <ID>4</ID>
        <ID>5</ID>
    </Users>
</Settings>'

上面的 XML 有一些 ID 值，我需要将这些值转换为可在临时表中用于执行连接的数据行.

The above XML has some ID values that I need to convert to rows of data that can be used in a temp table to perform joins against.

我的语法不太正确，我尝试了许多遇到的示例:

I can't quite get the syntax correct, I've tried a number of samples that I've come across:

SELECT T.r.value('.','int') as id
FROM @somexml.nodes('/Settings/Users') T(r)

返回:

|ID    |
|------|
|12345 |

以下内容:

SELECT T.r.query('.') as id
from @somexml.nodes('/Settings/Users/ID') as T(r) 

返回:

|ID         |
|-----------|
|<ID>1</ID> |
|<ID>2</ID> |
|<ID>3</ID> |
|<ID>4</ID> |
|<ID>5</ID> |

我接近了，但我想删除 XML 标签，只保留 ID 值，如下所示:

I'm getting close, but I want to remove the XML tags to just leave the ID values like so:

|ID |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

如果你想这样玩/解决，这里有一个小提琴:SQL Fiddle

Here's a fiddle if you want to play/solve that way: SQL Fiddle

一如既往地感谢任何帮助.

Any help is appreciated as always.

推荐答案

Replace Trquery('.') as id with Trvalue('.', 'INT') asid

Replace T.r.query('.') as id with T.r.value('.', 'INT') as id

这篇关于在 SQL Server 中将 XML 节点转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！