Query to select records from Xml column

Question

Post reply

Query to select records from Xml column

Paresh Dehadray

Mr or Mrs. 500

Points: 534
More actions
September 24, 2009 at 7:54 am

#103828

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 🙂

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

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

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 1

SELECT r.value('@Value','int')

FROM Job

CROSS APPLY JobXml.nodes('/Job/TimeID') AS x(r)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

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

Paresh Dehadray Mr or Mrs. 500 Points: 534 More actions · Answer 2

Thank you very much. It works. What a nice query!!!!

You are great!!! (champs of SQL Server) 🙂

avnish26 SSC Journeyman Points: 95 More actions · Answer 3

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>

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 4

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

nagarajaswamybm SSC Enthusiast Points: 123 More actions · Answer 5

Hello Everyone,

I have same requirement as the above one please look into it and suggest me the solution.

I have a table with 4 columns. out of which first three columns are nvarchar and last column is XML type.

Table Structure is given below,

FName LName Gender ItemPriceTag
A1 B1 M (Item = "Soap" Price=23), (Item = "Ball" Price=31), (Item = "Book" Price=98)
A2 B2 F (Item = "Soap" Price=45), (Item = "Ball" Price=19), (Item = "Book" Price=45)
A3 B3 M (Item = "Soap" Price=12), (Item = "Ball" Price=11), (Item = "Book" Price=18)
A4 B4 F (Item = "Soap" Price=54), (Item = "Ball" Price=65), (Item = "Book" Price=98)

I am stroring my first 3 columns as nvarchar, I want to store last column as XML type, becuase it may contain the data upto 250 items.

My first question is what is the proper structure to store the data in XML format. i am using the below format, I feel that is wrong, please look into it and suggest me whether it is correct or wrong. If it is wrong, please suggest the proper way to store the data.

for XML column im storing data as, (Since i am concerned on last column, i am mentioning only last column)
<ItemPriceTag><Item= "Soap"><Price>23</Price></ItemPriceTag>
<ItemPriceTag><Item= "Ball"><Price>31</Price></ItemPriceTag>
<ItemPriceTag><Item= "Book"><Price></Price></ItemPriceTag>. this is for first row

<ItemPriceTag><Item= "Soap"><Price>45</Price></ItemPriceTag>
<ItemPriceTag><Item= "Ball"><Price>19</Price></ItemPriceTag>
<ItemPriceTag><Item= "Book"><Price>45</Price></ItemPriceTag> this is for 2nd row .......etc

Second question is, I want to display each item as a column name and price as its value.
For eg:

FName LName Gender Soap Ball Price
A1 B1 M 23 31 98
A2 B2 F 45 19 45
A3 B3 M 12 11 18
A4 B4 F 54 65 98

Please suggest me the table structure and solution for the above requirement.

Thanks in advance,
Nagaraj