使用 SQL Server 从名称相同的 XML 节点获取所有数据 [英] Use SQL Server to get all the data from XML nodes named the same
问题描述
我有一个 XML 文件,我需要从中获取数据的节点的名称都相同.我了解如何访问第一条(或第二条记录),因此以下查询仅提供第二位作者(
标记).如何将所有作者作为一个列?
I have an XML file where the nodes that I need the data from are all named the same. I understand how to access the first (or second record) so the following query only gives me the second author (the <a1>
tag). How do I get all the authors as a single column ?
DECLARE @MyXML XML
SET @MyXML = '<refworks>
<reference>
<rt>Journal Article</rt>
<sr>Print(0)</sr>
<id>869</id>
<a1>Aabye,Martine G.</a1>
<a1>Hermansen,Thomas Stig</a1>
<a1>Ruhwald,Morten</a1>
<a1>PrayGod,George</a1>
<a1>Faurholt-Jepsen,Daniel</a1>
<a1>Jeremiah,Kidola</a1>
<a1>Faurholt-Jepsen,Maria</a1>
<a1>Range,Nyagosya</a1>
</reference>
</refworks>'
SELECT
author.value('(a1)[2]', 'varchar(MAX)') AS 'Author'
FROM @MyXML.nodes('/refworks/reference') AS ref(author)
推荐答案
试试这个 :-
SELECT
author.value('./text()[1]', 'varchar(MAX)') AS 'Author'
FROM @MyXML.nodes('//refworks/reference/child::node()') AS ref(author)
where author.value('local-name(.)[1]', 'varchar(100)') ='a1'
child::node() 表示轴说明符,它是 child
,::
是轴分隔符.
child::node() represents an axis specifier which is child
and ::
is the axis separator.
要了解用于在节点中向下钻取的子轴,可以在此MSDN 文档.
For understanding child axis which is used to drill down in the node can be found in this MSDN document.
更新:-
一个更简单的方法你在正确的轨道上.在 from 子句中指定子节点来过滤数据
A much simplier way You were on the right track .Specify the child node in the from clause for filtering the data
SELECT
author.value('(.)[1]', 'varchar(MAX)') AS 'Author'
FROM @MyXML.nodes('/refworks/reference/a1') AS ref(author)
这篇关于使用 SQL Server 从名称相同的 XML 节点获取所有数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!