retrieving XML nodes as rows

• MuraliKrishnan1980

  SSCrazy

  Points: 2497

  More actions

  November 14, 2011 at 2:28 am

  #246831

  [font="Tahoma"]

  Hello Friends,

  I have an xml file and I am trying to retrieve the value of particular nodes as rows. Please find below the sample format of the xml file.

  <p>

  <name>

  <sur></sur>

  <fn></fn>

  </name>

  <name>

  <sur></sur>

  <fn></fn>

  </name>

  <name>

  <sur></sur>

  <fn></fn>

  </name>

  </p>

  I am trying to retrieve the name present in the <fn> </fn> nodes. So the query should return 3 names...Please help me out in writing the query for this.. I would be happy even if i get a document reference on learning the method to retrieve the values.

  Thanks

  Murali

  [/font]

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  November 14, 2011 at 2:37 am

  #1407001

  See if this helps

  DECLARE @x XML

  SET @x='<p>

  <name>

  <sur></sur>

  <fn>AAA</fn>

  </name>

  <name>

  <sur></sur>

  <fn>BBB</fn>

  </name>

  <name>

  <sur></sur>

  <fn>CCC</fn>

  </name>

  </p>

  '

  SELECT a.b.value('.','VARCHAR(20)')

  FROM @x.nodes('/p/name/fn') AS a(b)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• MuraliKrishnan1980

  SSCrazy

  Points: 2497

  More actions

  November 14, 2011 at 2:47 am

  #1407007

  Thanks Mark.. That was what i was looking for.. Thanks for your help...