XQuery: Get the value only, and not the child node values

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 8, 2012 at 6:36 am

  #265182

  Hi Guys,

  If I have the following XML

  <ROOT>

  <SPECIALNEEDS>

  INCLUDE

  <NEED>

  BLIND

  </NEED>

  <NEED>

  BRAILLE AUDIO

  </NEED>

  </SPECIALNEEDS>

  </ROOT>

  How do I get the value "INCLUDE" only.

  xmltext.value('(ROOT/SPECIALNEEDS)[1]', nvarchar(max)) is giving me all the value contents (Include Blind Braille Audio)

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  November 8, 2012 at 6:45 am

  #1557152

  DECLARE @xml XML =

  '<ROOT>

  <SPECIALNEEDS>

  INCLUDE

  <NEED>

  BLIND

  </NEED>

  <NEED>

  BRAILLE AUDIO

  </NEED>

  </SPECIALNEEDS>

  </ROOT>'

  SELECT TD.D.value('./text()[1]','Varchar(400)')

  FROM @xml.nodes('/ROOT/SPECIALNEEDS') AS TD(D)

  Please note the whitespace preserved on the left of INCLUDE, as it's part of the node text.

  You can LTRIM it or make sure you XML formed appropriately eg:

  '<ROOT>

  <SPECIALNEEDS>INCLUDE

  <NEED>

  BLIND

  </NEED>

  <NEED>

  BRAILLE AUDIO

  </NEED>

  </SPECIALNEEDS>

  </ROOT>'

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  November 8, 2012 at 7:58 am

  #1557193

  Thanks Eugene, That was really helpful.

  Just because I am anal about these things 😉

  How would you write the select statement if the data was in a #temptable with a column of type XML

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  November 8, 2012 at 8:41 am

  #1557214

  Use CROSS APPLY

  DECLARE @xml XML =

  '<ROOT>

  <SPECIALNEEDS>

  INCLUDE

  <NEED>

  BLIND

  </NEED>

  <NEED>

  BRAILLE AUDIO

  </NEED>

  </SPECIALNEEDS>

  </ROOT>'

  SELECT @xml AS xmlCol

  INTO #temptable

  SELECT TD.D.value('./text()[1]','Varchar(400)')

  FROM #temptable

  CROSS APPLY xmlCol.nodes('/ROOT/SPECIALNEEDS') AS TD(D)

  ____________________________________________________

  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

• carlosaamaral

  Old Hand

  Points: 325

  More actions

  November 8, 2012 at 9:26 am

  #1557246

  LIVING AND LEARNING!!!!!!! 😀