XML Column Read

  • Hi,

    I have a column in table with xml type. I want to extract it row wise but it is giving me only first result

    My Table has following data

    id ItemId

    1 <itemids><itemid>259</itemid><itemid>489</itemid></itemids>

    2 <itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>

    3 <itemids><itemid>6603537</itemid></itemids>

    select

    tabId,

    itemid.value('(/itemids//itemid/node())[1]','int') AS ItemIDA

    from mytable;

    query is only returning first ItemId against every Id.

  • Based on your sample data: -

    IF object_id('tempdb..#yourTable') IS NOT NULL

    BEGIN

    DROP TABLE #yourTable;

    END;

    SELECT id, CAST(ItemId AS XML) AS ItemId

    INTO #yourTable

    FROM (VALUES(1, '<itemids><itemid>259</itemid><itemid>489</itemid></itemids>'),

    (2, '<itemids><itemid>6834262</itemid><itemid>489</itemid></itemids>'),

    (3, '<itemids><itemid>6603537</itemid></itemids>')

    )a(id, ItemId);

    You'd do it like this: -

    SELECT id, ParamValues.ItemId.value('.','INT') AS ItemId

    FROM #yourTable

    OUTER APPLY ItemId.nodes('/itemids/itemid') ParamValues(ItemId);


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • thank you. 🙂

    this is what I have implemented

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

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