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

Query to select records from Xml column Expand / Collapse
Author
Message
Posted Thursday, September 24, 2009 7:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 11:46 PM
Points: 6, Visits: 28
Hello Sir/Madam

I have following Xml in Job table which column name is JobXml (Xml datatype)

<Job>
<TimeID Value="2323"/>
<TimeID Value="4445"/>
<TimeID Value="3453"/>
<TimeID Value="7677"/>
</Job>

I want to write a select query who returns all TimeID Value like this

2323
4445
3453
7677

Please give me the query to select these records.
Currently I am using following query but it returns only one TimeID value:

select JobXml.value('(/Job/TimeID/@Value)[1]','int') from Job


Please help me.
Thanks in advance


Post #793276
Posted Thursday, September 24, 2009 8:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 6:25 AM
Points: 1,682, Visits: 19,599



SELECT r.value('@Value','int')
FROM Job
CROSS APPLY JobXml.nodes('/Job/TimeID') AS x(r)



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #793299
Posted Thursday, September 24, 2009 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 11:46 PM
Points: 6, Visits: 28
Thank you very much. It works. What a nice query!!!!
You are great!!! (champs of SQL Server)
Post #793304
Posted Thursday, November 5, 2009 11:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 6, 2014 12:07 AM
Points: 1, Visits: 66
Hello Expert,

Same above scenario i have...i have a table in which one column xmldata(having datatype xml) contain data in xml format, as given below:

<Employee>
<EMPID>1</EMPID>
<EMPNAME>AAA</EMPNAME>
<EMPSALARY>2000.00</EMPSALARY>
<DEPTID>1</DEPTID>
</Employee>

Now i have to read this xml data and display it with respective column names..like
EMPID EMPNAME EMPSALARY DEPTID
1 AAA 2000.00 1


please help..

Thanks in advance...

reagrds,
Avi
Post #814716
Posted Friday, November 6, 2009 3:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 1,893, Visits: 3,932
Hi Avi

To get the elements out of your XML you can use this:
DECLARE @xml XML

SELECT @xml = N'<Employee>
<EMPID>1</EMPID>
<EMPNAME>AAA</EMPNAME>
<EMPSALARY>2000.00</EMPSALARY>
<DEPTID>1</DEPTID>
</Employee>'

SELECT
T.C.value('(EMPID)[1]', 'int')
,T.C.value('(EMPNAME)[1]', 'nvarchar(20)')
,T.C.value('(EMPSALARY)[1]', 'decimal(15,5)')
,T.C.value('(DEPTID)[1]', 'int')
FROM @xml.nodes('Employee') T(C)

If you have to apply this to your table, have a look for CROSS APPLY with XML.

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #814784
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse