Post reply

Shredding XML

  • September 8, 2011 at 8:20 am

    #256815

    Hi,

    I'm new to working with XML data in SQL Server and i'm having some trouble getting the results i'm expecting so i wonder if someone can help.

    Consider the following:

    -- Setup a table that stores XML

    DECLARE @tableToHoldXML TABLE

    (

    xmlDataColumn XML

    )

    INSERT INTO @tableToHoldXML

    SELECT '<list>

    <item>1</item>

    <item>2</item>

    </list>'

    What i want to do is return the values (the 1 and the 2) of the item nodes as integers.

    So currently my query looks like this:

    -- Query to return the item nos.

    SELECT

    I.value('./@item','int') AS Item

    FROM

    @tableToHoldXML

    CROSS APPLY xmlDataColumn.nodes('/list/item') AS Items(I)

    But this yields two rows of NULL when i was expecting a 1 and a 2. Is anyone able to suggest how i can get what i'm after?

    Thanks,

    Simon


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • September 8, 2011 at 8:29 am

    #1378577

    It's ok.... cracked it.

    -- Query to return the item nos.

    SELECT

    convert(int,convert(varchar(50),I.query('text()'))) AS Item

    FROM

    @tableToHoldXML

    CROSS APPLY xmlDataColumn.nodes('/list/item') AS Items(I)

    Although if there is a more elegant solution i'd love to hear some suggestions.


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

  • September 8, 2011 at 8:34 am

    #1378589

    Also this

    SELECT

    I.value('(./text())[1]','int') AS Item

    FROM

    @tableToHoldXML

    CROSS APPLY xmlDataColumn.nodes('/list/item') AS Items(I)

    ____________________________________________________

    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

  • September 8, 2011 at 9:18 am

    #1378631

    Thanks, that's more what i was after. 🙂


    MCSE: Data Platform
    MCSE: Business Intelligence
    Follow me on Twitter: @WazzTheBadger
    LinkedIn Profile: Simon Osborne

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

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