Post reply

XQuery Returns Null

  • 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

    🙂

  • 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]

  • June 10, 2010 at 10:30 am

    #1178746

    Thanks Imu , It worked a magic

  • 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]

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply