XQuery get value from node root without child nodes

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  December 4, 2012 at 3:43 am

  #271467

  hi guys,

  Still trying to get my head around Xquery

  I have the following logic

  SET @CRM = (

  SELECT TOP 1 CRM FROM

  (

  SELECT

  cast(xmltext.query('/PARAMS/FILTERS/CRM')as nvarchar(max)) as 'CRM'

  FROM

  #XML

  ) X

  )

  Which returns me the following result

  <CRM>ONLY<TYPE>FSACLOSED</TYPE><TYPE>NRCLOSED</TYPE></CRM>

  I would like to get hold of just the 'ONLY' part of this string, i.e. only the value under the root of the node, not the child nodes or their values.

  Can I do this with XQuery or do I need to use string manipulation to isolate it?

• Mark Cowne

  One Orange Chip

  Points: 26944

  More actions

  December 4, 2012 at 4:01 am

  #1565203

  Change

  xmltext.query('/PARAMS/FILTERS/CRM')

  to

  xmltext.query('/PARAMS/FILTERS/CRM/text()')

  ____________________________________________________

  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