SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XQuery - using nodes & values - select the attribute vaules


XQuery - using nodes & values - select the attribute vaules

Author
Message
sridevi_mahalingam
sridevi_mahalingam
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 19
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
1 Srikar12 20090113T1705
2 nikil123 20090113T1705
3 supa123 20090113T1705

Please let me know if any one knows.
LutzM
LutzM
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24483 Visits: 13559
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
1 Srikar12 20090113T1705
2 nikil123 20090113T1705
3 supa123 20090113T1705
*/





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
sridevi_mahalingam
sridevi_mahalingam
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 19
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.
sridevi_mahalingam
sridevi_mahalingam
SSC Journeyman
SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)SSC Journeyman (99 reputation)

Group: General Forum Members
Points: 99 Visits: 19
thx. thx.. Its working fine.. :-)
Santhosh JKG
Santhosh JKG
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 15
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...
muralivision.dharan
muralivision.dharan
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 8
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search