Returning Multiple Rows From Querying Xml Column In Sql Server 2008

I have a table RDCAlerts with the following data in a column of type XML called AliasesValue: AKA

Solution 1:

Look at the .nodes() method in Books Online:

DECLARE@rTABLE (AliasesValue XML)
INSERTINTO@rSELECT'<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)

Solution 2:

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)

Share