Returning multiple rows from querying XML column in SQL Server 2008
Look at the .nodes() method in Books Online:
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)
You need to use the CROSS APPLY statement along with the .nodes() function to get multiple rows returned.
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)