Select statement to retrieve xml output as below

Question

Could someone provide me some clues or solution to retrieve record sets like below?

Note: I read the msdn documentation but leads me no where but hair loss :(

Just for the assumption think i have 2 tables connected via Rid field

Table 1 columns:

 Rid, UserName, Hash

Table 2 columns:

 Rid, Phone, City, Email

Table1 and Table2 are connected via the Rid column.

I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in SQL Server 2005 Express.

Output expected:

<UserDetails>
 <Account>
 <UserName>
 </UserName>
 <Hash>
 </Hash>
 </Account>
 <Personal>
 <Phone>
 </Phone>
 <City>
 </City>
 </Personal>
</UserDetails>

@matt Please look into the procedure I created below. When you execute the stored procedure with code at start you will know the problem I face

stack_getusers '<Request Type="GetUsers" CRUD="R">
 <UserDetails>
 <Rid></Rid>
 </UserDetails>
 </Request>'
CREATE PROCEDURE [dbo].[stack_getusers] 
@doc NTEXT 
AS 
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc 
SELECT 
 t1.UserName AS "Account/UserName",
 t1.Hash AS "Account/Hash",
 (
 SELECT t2.Phone AS "Personal/Phone",
 t2.City AS "Personal/City"
 FROM table1 t2
 INNER JOIN table2 t3 ON t2.rid = t3.rid WHERE t2.rid = t1.rid AND (xml.Rid = '' OR t1.rid = xml.Rid)
 FOR XML PATH('Personals')
 )
FROM table1 t1
 INNER JOIN table2 t2 ON t1.rid = t2.rid
OPENXML (@idoc,'/Request/Users',2)
WITH (Rid int) as xml
where (xml.Rid = '' OR t1.rid = xml.Rid)
FOR XML PATH ('UserDetails');
EXEC sp_xml_removedocument @idoc

Answer 1

Give this a try:

SELECT 
 t1.UserName AS "Account/UserName",
 t1.Hash AS "Account/Hash",
 t2.Phone AS "Personal/Phone",
 t2.City AS "Personal/City"
FROM table1 t1
 INNER JOIN table2 t2
 ON t1.rid = t2.rid
FOR XML PATH ('UserDetails');

I have added a second code sample in response to your comment. I have no idea if this will work, or if it is even the best way to accomplish what you want it to. Please let me know.

SELECT 
 t1.UserName AS "Account/UserName",
 t1.Hash AS "Account/Hash",
 (
 SELECT
 t2.Phone AS "Personal/Phone",
 t2.City AS "Personal/City"
 FROM table1 t2
 INNER JOIN table2 t3
 ON t2.rid = t3.rid
 WHERE t2.rid = t1.rid
 FOR XML PATH('Personals')
 )
FROM table1 t1
 INNER JOIN table2 t2
 ON t1.rid = t2.rid
FOR XML PATH ('UserDetails');