使用SQL Server从命名为相同的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 ?

我有一个XML文件，其中需要数据的节点都被命名为相同的。我理解如何访问第一个(或第二个记录)，因此下面的查询只给我第二个作者( 标记)。我如何把所有的作者作为一个专栏?

      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)

1 个解决方案

#1

Try this :-

试试这个:

  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() represents an axis specifier which is child and :: is the axis separator.

::node()表示一个axis说明符，它是child和::是axis分隔符。

For understanding child axis which is used to drill down in the node can be found in this MSDN document.

为了理解用于在节点中向下钻取的子轴，可以在这个MSDN文档中找到。

or manipulating xml data in sql server

或者在sql server中操作xml数据

Updated :-

更新:

A much simplier way You were on the right track .Specify the child node in the from clause for filtering the data

这是一种非常简单的方法，可以在from子句中指定子节点来过滤数据

 SELECT 
      author.value('(.)[1]', 'varchar(MAX)') AS 'Author'
  FROM @MyXML.nodes('/refworks/reference/a1') AS ref(author)

#1