Convert XML Data to Tabular and XML Form in SQL Server 2005 -

this xml

<roomtypes>   <roomtype id="1" total="5">     <roomfacilities>       <fid>101</fid>       <fid>103</fid>       <fid>105</fid>     </roomfacilities>   </roomtype>   <roomtype id="2" total="4">     <roomfacilities>       <fid>108</fid>       <fid>109</fid>       <fid>110</fid>     </roomfacilities>   </roomtype> </roomtypes> 

i need make xquery display result this

int int xml 1   5   <fid>101</fid><fid>103</fid><fid>105</fid> 2   4   <fid>108</fid><fid>109</fid><fid>110</fid> 

i have reached here..

int int xml 1   5   <fid>101</fid><fid>103</fid><fid>105</fid> 2   4   <fid>101</fid><fid>103</fid><fid>105</fid> 

using this

declare @xmldocument xml; set @xmldocument =( select roomtypes hotels);  declare @tb table(id int, total int, fid xml); declare @id int; set @id=0; insert @tb (id, total,fid)  select       p.value('../@id', 'int'),     p.value('../@total', 'int'),     p.query('//roomtype[@id=1]/roomfacilities/fid')  @xmldocument.nodes('roomtypes/roomtype/roomfacilities') n(p) select * @tb; 

since have hardcoded @id=1 showing result of first element only.

any appreciated...

select       p.value('../@id', 'int'),     p.value('../@total', 'int'),     p.query('fid') @xmldocument.nodes('roomtypes/roomtype/roomfacilities') n(p)