使用可变数量的节点粉碎 xml

Question

我有一个 xml 列，其中包含如下数据：

<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>

作者数量是可变的。我可以毫无问题地提取第一作者等：

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)

我只是好奇如何“动态”地实现这样的事情：

1 JL Alió JL 1 D 奥尔蒂斯 D 1 O 穆夫托格鲁 O 1 MJ 加西亚 MJ

谢谢！

Answer 1

这似乎可以完成工作：

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)