Post reply

XQuery - using nodes & values - select the attribute vaules

  • June 19, 2009 at 12:03 am

    #222809

    I have the table which has the two fields

    CREATE TABLE AgentQueue ( customerId VARCHAR(15) NOT NULL,QueueXML XML NOT NULL )

    Queue XMl has the format like

    I am using SQL server 2005.I need the output in below mentioed format using Xquery with in SQL server stored Procedure.

    Number Name Date

    1Srikar1220090113T1705

    2nikil12320090113T1705

    3supa12320090113T1705

    Please let me know if any one knows.

  • June 19, 2009 at 5:32 am

    #1012283

    The following might help you:

    DECLARE @AgentQueue TABLE ( customerId VARCHAR(15) NOT NULL,QueueXML XML NOT NULL )

    INSERT INTO @AgentQueue

    SELECT '1', '

    '

    SELECT

    t.c.value ('@Number[1]','varchar(50)') AS Number,

    t.c.value ('@Name[1]','varchar(50)') AS Name,

    t.c.value ('@Date[1]','varchar(50)') AS Date

    FROM @AgentQueue agent

    CROSS APPLY

    QueueXML.nodes('/TransactionResp/Queue/QueueList/Item') as t(c)

    /*Result set

    Number Name Date

    1Srikar1220090113T1705

    2nikil12320090113T1705

    3supa12320090113T1705

    */


    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 19, 2009 at 5:58 am

    #1012298

    Thx.

    Will It work in SQL server. Bcos i am getting the exception as "Cannot find either column "t" or the user-defined function or aggregate "t.c.value", or the name is ambiguous."

    Please help me.

  • June 19, 2009 at 6:12 am

    #1012310

    thx. thx.. Its working fine.. 🙂

  • March 4, 2011 at 2:07 am

    #1294510

    Hi

    Can any one give me the proper approach for this... in case any attribute missing in the xml node means, how the xquery will handle read the xml node.

    <root>

    <child id="1" name="name1" cat="Bus" ItemId="001"/>

    <child id="2" name="name2" cat="Car" ItemId="002"/>

    <child id="3" name="name3" cat="Train" />

    <child id="4" name="name4" cat="Air" ItemId="005"/>

    <child id="5" name="name5" cat="Bike" ItemId="022"/>

    </root>

    I want to read xml node by node based on ItemId, in this case itemId is missing in the 3rd node.

    When I used xquery to read this xml, it's working upto 2nd child node, after that its reading the 4th node'd itemId, then giving problem....

    Please any one help me to solve this problem...

  • February 8, 2014 at 6:58 am

    #1687317

    hi,

    can you please tell me how it worked.because im also getting the same error.'

    Cannot find either column "T" or the user-defined function or aggregate "T.C.value", or the name is ambiguous.'

    I dont know what is wrong .can you please help me very urgent

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

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