February 25, 2009 at 2:08 pm
I want data of 2nd select statement instead of t2.order_data in my first select statement.
select t1.order_date,
t1.solution_id, t2.order_data, t2.price,
t3.description
from Orders t1, Order_Detail t2, Order_Status t3
where t1.order_id=t2.order_id and
t1.order_status_id = t3.order_status_id and t1.username in('xyz') and (t1.order_status_id=1 or t1.order_status_id=2)
SELECT ('data(//zip)')
AS Zip,order_data.query('data(//Category)')
As Category
FROM Order_Detail
February 25, 2009 at 2:13 pm
You can put a sub-query in the Select clause. Put parentheses around it and treat it as a column.
For example:
select Col1,
(select ColA, ColB
from dbo.Table2
where ColC = Table1.Col1
for XML raw, type) as XMLCol
from dbo.Table1;
The data from the sub-query will be included as a column in the main (outer) query.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 2:29 pm
can you please make it more clear with my statement.
Thankyou
February 25, 2009 at 2:35 pm
Nope. I don't understand what your second query is supposed to do, so I really can't do much with it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 2:41 pm
my second query selects value from xml,for e.g.
order_data.query('data(//zip)')
selects
from order_data column
February 25, 2009 at 2:57 pm
So long as the XML doesn't have multiple nodes, you can put the XQuery portions of the second query right into the first query. I thought you were going the other way around.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply