Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XQuery - using nodes & values - select the attribute vaules Expand / Collapse
Author
Message
Posted Friday, June 19, 2009 12:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:34 AM
Points: 5, 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.
Post #738067
Posted Friday, June 19, 2009 5:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:11 PM
Points: 7,019, Visits: 12,909
The following might help you:
DECLARE @AgentQueue TABLE  ( customerId VARCHAR(15) NOT NULL,	QueueXML XML NOT NULL )
INSERT INTO @AgentQueue
SELECT '1', '<TransactionResp>
<Queue>
<QueueList customerId="cust123" tokenNumber="218">
<Item Number="1" Name="Srikar12" Date="20090113T1705"/>
<Item Number="2" Name="nikil123" Date="20090113T1705"/>
<Item Number="3" Name="supa123" Date="20090113T1705"/>
</QueueList> </Queue> </TransactionResp>'

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
Post #738185
Posted Friday, June 19, 2009 5:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:34 AM
Points: 5, 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.
Post #738203
Posted Friday, June 19, 2009 6:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 25, 2009 4:34 AM
Points: 5, Visits: 19
thx. thx.. Its working fine..
Post #738217
Posted Friday, March 4, 2011 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 2, 2012 12:51 AM
Points: 1, 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...
Post #1073148
Posted Saturday, February 8, 2014 6:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 8, 2014 6:58 AM
Points: 1, 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
Post #1539496
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse