分析SQL Server存储过程中的XML

Question

我有

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只列出一个像这样的附属机构时，它过去工作得很好

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

然而，它已经将Affiliation更改为多节点

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

我的问题是，我如何更改代码以连接所有附属关系并将其添加到插入中？

Answer 1

请尝试以下解决方案。

主要的变化是取代了这一行：

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

它将如下：

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