从 SQL Server 2008 中查询 XML 列返回多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9873192/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning multiple rows from querying XML column in SQL Server 2008
提问by Hugo Forte
I have a table RDCAlertswith the following data in a column of type XMLcalled AliasesValue:
我RDCAlerts在XML名为的列中包含以下数据的表AliasesValue：
<aliases>
<alias>
<aliasType>AKA</aliasType>
<aliasName>Pramod Singh</aliasName>
</alias>
<alias>
<aliasType>AKA</aliasType>
<aliasName>Bijoy Bora</aliasName>
</alias>
</aliases>
I would like to create a query that returns two rows - one for each alias and I've tried the following query:
我想创建一个返回两行的查询 - 每个别名一个，我尝试了以下查询：
SELECT
AliasesValue.query('data(/aliases/alias/aliasType)'),
AliasesValue.query('data(/aliases/alias/aliasName)'),
FROM [RdcAlerts]
but it returns just one row like this:
但它只返回一行，如下所示：
AKA AKA | Pramod Singh Bijoy Bora
回答by Stuart Ainsworth
Look at the .nodes() method in Books Online:
查看联机丛书中的 .nodes() 方法：
DECLARE @r TABLE (AliasesValue XML)
INSERT INTO @r
SELECT '<aliases> <alias> <aliasType>AKA</aliasType> <aliasName>Pramod Singh</aliasName> </alias> <alias> <aliasType>AKA</aliasType> <aliasName>Bijoy Bora</aliasName> </alias> </aliases> '
SELECT c.query('data(aliasType)'), c.query('data(aliasName)')
FROM @r r CROSS APPLY AliasesValue.nodes('aliases/alias') x(c)
回答by Derek Kromm
You need to use the CROSS APPLYstatement along with the .nodes()function to get multiple rows returned.
您需要将CROSS APPLY语句与.nodes()函数一起使用才能返回多行。
select
a.alias.value('(aliasType/text())[1]', 'varchar(20)') as 'aliasType',
a.alias.value('(aliasName/text())[1]', 'varchar(20)') as 'aliasName'
from
RDCAlerts r
cross apply r.AliasesValue.nodes('/aliases/alias') a(alias)
