July 21, 2010 at 5:37 pm
Hi, I'm trying to create a query which joins a table to a FOR XML generated XML.
Below is greatly simplified query made up as I'd instinctively create a query. I'm trying to produce some columns from a traditional query, and also a column of XML containing the related details (in XML), with multiple rows each having their own XML data in the 3rd column.
Select o.OrderId, o.OrderTitle, od.Xml --second column being the output of the FOR XML query
From Orders o
inner join (Select OrderId, Name, Description From OrderDetail FOR XML AUTO) od
ON o.OrderId = od.OrderId -- somehow joining to the XML
Can someone kindly suggest how to achieve this?
Thanks,
+M
July 21, 2010 at 5:49 pm
This is usually done using (yes, I am going to really say it) a correlated subquery instead.
Try it doing something like:
Select o.OrderId, o.OrderTitle,
(Select OrderId, Name, Description
From OrderDetail
where orderdetail.orderid=order.orderid
FOR XML AUTO, type) as XMLCol
From Orders o
The "type" is what converts the subquery back into an XML snippet to include.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 22, 2010 at 4:23 pm
Aaahhhh perfect, thanks so much!
July 22, 2010 at 8:02 pm
mark-list (7/22/2010)
Aaahhhh perfect, thanks so much!
Happy to help!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply