SQL Server存储过程解析PubMed XML 多Affiliation节点拼接方法咨询

我有如下代码：

INSERT INTO [PubMed.Author] (pmid, ValidYN, LastName, FirstName, ForeName, Suffix, Initials, Affiliation)
    SELECT
        pmid, 
        nref.value('@ValidYN', 'varchar(max)') ValidYN, 
        nref.value('LastName[1]', 'varchar(max)') LastName, 
        nref.value('FirstName[1]', 'varchar(max)') FirstName,
        nref.value('ForeName[1]', 'varchar(max)') ForeName,
        nref.value('Suffix[1]', 'varchar(max)') Suffix,
        nref.value('Initials[1]', 'varchar(max)') Initials,
        nref.value('Affiliation[1]', 'varchar(max)') Affiliation
    FROM
        [Publication.PubMed.XML] 
    CROSS APPLY
        XMLData.nodes('//AuthorList/Author') AS R(nref)
    WHERE
        pmid = @pmid

过往当PubMed的XML仅包含单个affiliation节点时，这段代码可以正常运行，对应XML结构示例如下：

<Author ValidYN="Y">
    <LastName>Smith</LastName>
    <ForeName>John</ForeName>
    <Initials>J</Initials>
    <Affiliation>Department of Medicine, XYZ, NY USA; The Stony Brook Cancer Center, Stony Brook, NY 
11794, USA.</Affiliation>  
</Author>

但目前PubMed的XML结构已调整，Affiliation改为嵌套在多个AffiliationInfo节点中，新的XML结构示例如下：

<Author ValidYN="Y">
    <LastName>Smith</LastName>
    <ForeName>John</ForeName>
    <Initials>J</Initials>
    <AffiliationInfo> 
        <Affiliation>Department of Medicine, XYZ, NY USA; </Affiliation> 
    </AffiliationInfo>
    <AffiliationInfo>
        <Affiliation>The Stony Brook Cancer Center, Stony Brook, NY 11794, USA.</Affiliation>  
   </AffiliationInfo>
</Author>

请问需要如何修改原有代码，才可以将同一个Author下的所有Affiliation内容拼接后插入到数据库表中？

直接修改原SELECT语句中Affiliation字段的取值逻辑即可，修改后的完整代码如下：

INSERT INTO [PubMed.Author] (pmid, ValidYN, LastName, FirstName, ForeName, Suffix, Initials, Affiliation)
    SELECT
        pmid, 
        nref.value('@ValidYN', 'varchar(max)') ValidYN, 
        nref.value('LastName[1]', 'varchar(max)') LastName, 
        nref.value('FirstName[1]', 'varchar(max)') FirstName,
        nref.value('ForeName[1]', 'varchar(max)') ForeName,
        nref.value('Suffix[1]', 'varchar(max)') Suffix,
        nref.value('Initials[1]', 'varchar(max)') Initials,
        -- 拼接所有Affiliation内容，分隔符可自行调整
        STUFF((
            SELECT '; ' + aff_node.value('.', 'varchar(max)')
            FROM R.nref.nodes('AffiliationInfo/Affiliation') AS Aff(aff_node)
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)'), 1, 2, '') AS Affiliation
    FROM
        [Publication.PubMed.XML] 
    CROSS APPLY
        XMLData.nodes('//AuthorList/Author') AS R(nref)
    WHERE
        pmid = @pmid

改动说明 #

• 子查询通过nodes('AffiliationInfo/Affiliation')拿到当前作者节点下所有嵌套的Affiliation节点
• 用FOR XML PATH('')实现多节点文本拼接，默认用; 作为不同机构的分隔符，可根据需求调整
• STUFF函数用于删除开头多余的分隔符
• 如果你使用的是SQL Server 2017及以上版本，可以替换为更简洁的STRING_AGG写法，性能也更优：

STRING_AGG(aff_node.value('.', 'varchar(max)'), '; ') AS Affiliation

内容的提问来源于stack exchange，提问作者Bill