在 SQL 存储过程中连接 XML

Question

我有从NIH下载的xml文件的这一部分

<PubmedArticle>
<MedlineCitation Status="In-Data-Review" Owner="NLM">
<PMID Version="1">31202264</PMID>   
<Article PubModel="Electronic"> 
<AuthorList CompleteYN="Y">
<Author ValidYN="Y">
<LastName>Jg</LastName>
<ForeName>Zg</ForeName>
<Initials>Z</Initials>
<AffiliationInfo>
<Affiliation>State Key Laboratory of ...</Affiliation>
</AffiliationInfo>
<AffiliationInfo>
<Affiliation>College of Physics, ...</Affiliation>
</AffiliationInfo>
</Author>
<Author ValidYN="Y">
<LastName>Tn</LastName>
<ForeName>L</ForeName>
<Initials>L</Initials>
<AffiliationInfo>
<Affiliation>State Key Laboratory of ...</Affiliation>
</AffiliationInfo>
</Author>      
</AuthorList>    
</Article>   
</MedlineCitation> 
</PubmedArticle>

如果有多个隶属关系节点，我需要合并隶属关系节点。

在存储过程中，我传入了 PMID，并且我一直在使用

Affiliation = COALESCE(nref.value('AffiliationInfo[1]/Affiliation[1]','varchar(max)'),
nref.value('Affiliation[1]','varchar(max)')),
from [Publication.PubMed.AllXML] cross apply x.nodes('//AuthorList/Author') as R(nref)
where pmid = @pmid

拉动第一个元素可以正常工作，但我想将每个作者的每个隶属关系合并在一行中，例如

affiliation = "State Key Laboratory of ... +';' + College of Physics, ...

或者只是

affiliation = "State Key Laboratory of ..."

如果只有一个。

我试过了

STUFF((SELECT ';' + R.nref.value('.', 'NVARCHAR(MAX)')
FROM   x.nodes('./AffiliationInfo/Affiliation')  R(nref)
FOR XML PATH(''),TYPE).value('.', 'NVARCHAR(MAX)'),
1, 1, '')
FROM  [Publication.PubMed.Author]  au
JOIN  [Publication.PubMed.AllXML] a ON a.pmid = au.pmid     
cross apply x.nodes('//AuthorList/Author') as R(nref)
WHERE au.pmid = 31202264

但我不确定我的参考资料是否正确。 我为每个作者得到一个空值。

仅供参考 - [Publication.PubMed.AllXML] 是存储 xml 文件的地方

和

[Publication.PubMed.Author] 是数据存储在数据库中的位置。

感谢您的帮助。

Answer 1

不幸的是，SQL Server 仍然不支持 string-join(( XPath 2.0 函数。 所以两步过程与CTE来救援。

.SQL

-- DDL and data population, start
DECLARE @tbl TABLE (ID INT IDENTITY(1,1) PRIMARY KEY, xmlData XML);
INSERT INTO @tbl(xmlDATA)
VALUES
(N'<PubmedArticle>
<MedlineCitation Status="In-Data-Review" Owner="NLM">
<PMID Version="1">31202264</PMID>
<Article PubModel="Electronic">
<AuthorList CompleteYN="Y">
<Author ValidYN="Y">
<LastName>Jg</LastName>
<ForeName>Zg</ForeName>
<Initials>Z</Initials>
<AffiliationInfo>
<Affiliation>State Key Laboratory of ...</Affiliation>
</AffiliationInfo>
<AffiliationInfo>
<Affiliation>College of Physics, ...</Affiliation>
</AffiliationInfo>
</Author>
<Author ValidYN="Y">
<LastName>Tn</LastName>
<ForeName>L</ForeName>
<Initials>L</Initials>
<AffiliationInfo>
<Affiliation>State Key Laboratory of ...</Affiliation>
</AffiliationInfo>
</Author>
</AuthorList>
</Article>
</MedlineCitation>
</PubmedArticle>');
-- DDL and data population, end
;WITH rs AS
(
SELECT col.value('(LastName)[1]','VARCHAR(30)') AS LastName
,col.query('AffiliationInfo/Affiliation') AS affiliationXML
FROM @tbl AS tbl
CROSS APPLY tbl.[xmlData].nodes('/PubmedArticle/MedlineCitation/Article/AuthorList/Author') tab(col)
)
SELECT LastName
, SUBSTRING((SELECT ';' + col.value('.', 'VARCHAR(1024)')
FROM rs.affiliationXML.nodes('Affiliation') tab(col)
FOR XML PATh('')), 2, 1024) AS Affiliation
FROM rs;