CODEWITHSUNDEEP
sql-serverxmlnodes
user918967
user918967

Reputation: 2167

Use SQL Server to get all the data from XML nodes named the same

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)

Upvotes: 8

Views: 29528

Answers (1)

praveen
praveen

Reputation: 12271

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.

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

or manipulating xml data in sql server

Updated :-

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)

Upvotes: 16

Related Questions