Returning multiple rows from querying XML column in SQL Server 2008

Question

I have a table RDCAlerts with the following data in a column of type XML called 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

Stuart Ainsworth · Accepted Answer · 2012-03-26 13:45:34Z

25

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)

answered Mar 26, 2012 at 13:45

Stuart Ainsworth

12.9k44 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PKD Over a year ago

It's 5 years after you wrote your answer... and it's just helped me solve an issue I was having. Thanks!

CarlGanz Over a year ago

The gift that keeps on giving!

Derek · Accepted Answer · 2012-03-26 13:46:12Z

21

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)

answered Mar 26, 2012 at 13:46

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Collectives™ on Stack Overflow

Returning multiple rows from querying XML column in SQL Server 2008

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related