Parse XML in a SQL Server stored procedure

Question

I have

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

And it used to work fine when PubMed only listed one affiliation like

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

However it has changed Affiliation to a multiple node

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

My question is how can I change my code to concatenate all of the Affiliations and add it to my insert?

Answer 1

Please try the following solution.

The main change is instead of this line:

nref.value('Affiliation[1]','varchar(max)') Affiliation

it will be as follows:

nref.query('data(AffiliationInfo/Affiliation/text())').value('.','varchar(max)') Affiliation

SQL

DECLARE @xml XML = 
N'<Author ValidYN="Y">
    <LastName>Smith</LastName>
    <FirstName>John</FirstName>
    <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>';

SELECT nref.value('@ValidYN','varchar(max)') AS ValidYN
    , nref.value('(LastName/text())[1]','varchar(max)') AS LastName
    , nref.value('(FirstName/text())[1]','varchar(max)') AS FirstName
    , nref.value('(ForeName/text())[1]','varchar(max)') AS ForeName
    , nref.value('(Suffix/text())[1]','varchar(max)') AS Suffix
    , nref.value('(Initials/text())[1]','varchar(max)') AS Initials
    , nref.query('data(AffiliationInfo/Affiliation/text())').value('.','varchar(max)') AS Affiliation
FROM @xml.nodes('/Author') AS t(nref);

SQL #2

SELECT nref.value('@ValidYN','varchar(max)') AS ValidYN
    , nref.value('(LastName/text())[1]','varchar(max)') AS LastName
    , nref.value('(FirstName/text())[1]','varchar(max)') AS FirstName
    , nref.value('(ForeName/text())[1]','varchar(max)') AS ForeName
    , nref.value('(Suffix/text())[1]','varchar(max)') AS Suffix
    , nref.value('(Initials/text())[1]','varchar(max)') AS Initials
    , nref.query('
        for $r in AffiliationInfo/Affiliation/text()
        let $token := concat("[", $r, "]")
        return $token
        ').value('text()[1]','varchar(max)')AS Affiliation
FROM @xml.nodes('/Author') AS t(nref);