XQuery Returns Null

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  June 10, 2010 at 6:25 am

  #236280

  Hi All

  I am having an XML packet as shown below, when i try to select value using the SQL below i am always getting null as a value, can any one point me out where i am making a mistake, i am been stuck with this.

  Declare @X XML

  Select @X='<?xml version="1.0" encoding="ISO-8859-1"?>

  <CONTENT>

  <PROVIDER>ABC</PROVIDER>

  <GAME>Poker</GAME>

  <MATCH_TITLE>News</MATCH_TITLE>

  <TEAMS_INVOLVED>NewsStart</TEAMS_INVOLVED>

  <EVENT_TYPE>Poker UPDATES</EVENT_TYPE>

  <CLIENT_ID>000000</CLIENT_ID>

  <ALERTTEXT>This is what i wnat to get </ALERTTEXT>

  <TIMESTAMP>10 May 2010 15:05:35</TIMESTAMP>

  </CONTENT> '

  WITH XMLNAMESPACES(

  DEFAULT 'urnchemas-microsoft-comqlqlRowSet2'

  )

  SELECT x.value('ALERTTEXT[1]','varchar(3999)') from @x.nodes('/CONTENT/PROVIDER')v(x)

  All i need to get is the Alerttext tab,

  Thanks in advance for your help

  Cheers

  

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 10, 2010 at 9:41 am

  #1178698

  Two minor fixes: 1) remove the namespace declaration since this is an untyped xml document and 2) remove the "/PROVIDER" from your nodes declaration since this tag is closed before.

  --;WITH XMLNAMESPACES(

  --DEFAULT 'urnchemas-microsoft-comqlqlRowSet2'

  --)

  SELECT x.value('ALERTTEXT[1]','varchar(3999)') from @x.nodes('/CONTENT')v(x)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  June 10, 2010 at 10:30 am

  #1178746

  Thanks Imu , It worked a magic

• LutzM

  SSC Guru

  Points: 107049

  More actions

  June 10, 2010 at 10:40 am

  #1178755

  Glad I could help

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]