shred xml with variable number of nodes

Question

Hi,

I have an xml column which contain data like this:

<AuthorList CompleteYN="Y">
<Author ValidYN="Y">
<LastName>Alió</LastName>
<ForeName>J L</ForeName>
<Initials>JL</Initials>
</Author>
<Author ValidYN="Y">
<LastName>Ortiz</LastName>
<ForeName>D</ForeName>
<Initials>D</Initials>
</Author>
<Author ValidYN="Y">
<LastName>Muftuoglu</LastName>
<ForeName>O</ForeName>
<Initials>O</Initials>
</Author>
<Author ValidYN="Y">
<LastName>Garcia</LastName>
<ForeName>M J</ForeName>
<Initials>MJ</Initials>
</Author>
</AuthorList>

The number of Authors is variable. I have no problem to extract the first authors etc.:

SELECT
 ID,
 AuthorList.value('(Author/ForeName)[1]', 'varchar(max)') as ForeName,
 AuthorList.value('(Author/LastName)[1]', 'varchar(max)') as LastName,
 AuthorList.value('(Author/Initials)[1]', 'varchar(max)') as Initials
FROM   
      XMLs CROSS APPLY 
      xml.nodes('//AuthorList') AS AuthorList(AuthorList)

I am just curious how I can achieve something like this ‘dynamically’:

1 J L Alió JL 1 D Ortiz D 1 O Muftuoglu O 1 M J Garcia MJ

Thanks!

Answer 1

This seems to do the job:

select 
    AuthorList.value('(ForeName/text())[1]', 'varchar(max)') as ForeName,
    AuthorList.value('(LastName/text())[1]', 'varchar(max)') as LastName,
    AuthorList.value('(Initials/text())[1]', 'varchar(max)') as Initials
from 
    XMLs CROSS APPLY 
      xml.nodes('//AuthorList/Author') AS AuthorList(AuthorList)