The
FOR XML
option always translates the resulting table row by row. The
SELECT
statemtent you're using will result in:
Property RoomId
-------------------------
1 51
1 53
1 65
2 51
2 481
2 101739578
To get the XML structure as needed you have to create a query that will result in:
Property RoomId RoomID RoomId
--------------------------------------------------------
1 51 53 65
2 51 481 101739578
This isn't possible because you can't have the same column name more than once, even if you're using a PIVOT statement to turn the RoomIds form rows to columns.
You only can get the RoomIds at same level in a subset like this:
<transaction>
<propertydataset>
<property>1</property>
<Rooms>
<roomid>51</property>
<roomid>53</property>
<roomid>65</property>
</Rooms>
</propertydataset>
<propertydataset>
<property>2</property>
<Rooms>
<roomid>51</property>
<roomid>481</property>
<roomid>101739578</property>
</Rooms>
</propertydataset>
</transaction>
Look here for how to create nested XML queries:
Nested FOR XML results with SQL Server’s PATH mode – Richard Dingwall[
^]