Post reply

XML Nodes Problem

  • May 14, 2009 at 9:47 am

    #209768

    Hi,

    I am having a problem display a result when using XML nodes. Here is my script...

    -- DROP TABLE #XmlStatusRequest

    CREATE TABLE XmlStatusRequest (ID INT IDENTITY(1,1), RequestString XML)

    DECLARE @GetStatusRequest XML

    SET @GetStatusRequest = '

    0.4.0.2.3.0.1

    GB

    LEA01

    DR0016

    CSPID

    20081110101044Z

    DR0013

    '

    INSERT INTO XmlStatusRequest (RequestString)

    SELECT @GetStatusRequest

    SELECT tab.col.value('./countryCode[1]','VARCHAR(100)') AS 'Value'

    FROM XmlStatusRequest

    CROSS APPLY

    RequestString.nodes('/retainedDataMessage/retainedDataHeader/requestID') AS tab(col)

    GO

    The problem is arrising as even though has a value of 'GB' is does not display. If however I slightly modify the first XML Attribute from:

    To this

    Then it works fine.

    Any help would be great.

    Thanks

  • May 14, 2009 at 9:50 am

    #994656

    I cant seem to ppost the XML correctly, I've added an attachment to show this.

  • May 14, 2009 at 10:34 am

    #994697

    Hi,

    the problem is that you're selecting from an XML string with a namespace.

    You need to declare the namespace in your select statement.

    Running the following code should return 'GB'.

    Note: Make sure you have the semicolon in front of the WITH keyword since this keyword needs to be the first one within a statement.

    ;WITH XMLNAMESPACES (

    DEFAULT 'Local RDHI'

    )

    SELECT tab.col.value('./countryCode[1]','VARCHAR(100)') AS 'Value'

    FROM #XmlStatusRequest

    CROSS APPLY

    RequestString.nodes('/retainedDataMessage/retainedDataHeader/requestID') AS tab(col)


    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]

  • May 14, 2009 at 10:37 am

    #994700

    Lutz, thanks you so much! Been trying to get that to work all day!.

    Thanks again

  • May 14, 2009 at 11:15 am

    #994738

    You're welcome. 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]

  • May 14, 2009 at 1:22 pm

    #994832

    Cwalton (5/14/2009)

    I cant seem to ppost the XML correctly, I've added an attachment to show this.

    You need to use the code tags: ..

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 6 posts - 1 through 6 (of 6 total)

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