FOR XML PATH query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

FOR XML PATH query

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-04-06 : 07:31:44

I've forgotten how to write a for xml path query when joining multiple tables and can't seem to find an example anywhere. Help would be greatly apprecitated:

use [master]
go
drop database test
go
create database test
go
use test
go
drop table #order
drop table #orderitem
go
create table #order (id int, data varchar(10))
create table #orderitem (id int, itemid int, otherdata varchar(20))
go
insert into #order
select 1, 'qwerty'
union all
select 2, 'asdfg'

insert into #orderitem
select 1, 1, 'eeeeeeeee'
union all
select 1,2, 'rrrrrrrr'
union all
select 2,1,'aaaaaaaa'
go
-- Desired output
<order>
	<id></id>
	<data></data>
	<items>
		<item>
			<itemid></itemid>
			<orderdata></otderadata>
		</item>
		<item>
			<itemid></itemid>
			<orderdata></otderadata>
		</item>
	</items>
</order>

Hearty head pats

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-04-06 : 07:51:46

SELECT *
FROM #order [order]
JOIN #orderitem OI
ON [order].id = OI.id
FOR XML AUTO, ELEMENTS

Iam a slow walker but i never walk back

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-04-06 : 09:50:45

Hi there

Thanks for your reply. Don't suppose you know how to do it using FOR XML PATH? The reason being is that the query I have to actually write is a tad more complex and can't be achieved using FOR XML AUTO....

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 12:05:49

[code]
select *,(select * from #orderitem where id=o.id for XML path ('item'),root('items'),type)
from #order o
for xml path('order'),ROOT('orders')

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bex
Aged Yak Warrior

580 Posts

Posted - 2010-04-07 : 09:13:27

Thankyou

Hearty head pats

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:00:08

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources