Select statement to retrieve xml output as below 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:

&lt;UserDetails&gt;
    &lt;Account&gt;
        &lt;UserName&gt;
        &lt;/UserName&gt;
        &lt;Hash&gt;
        &lt;/Hash&gt;
    &lt;/Account&gt;
    &lt;Personal&gt;
        &lt;Phone&gt;
        &lt;/Phone&gt;
        &lt;City&gt;
        &lt;/City&gt;
    &lt;/Personal&gt;
&lt;/UserDetails&gt;


@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  '&lt;Request Type="GetUsers" CRUD="R"&gt;
                            &lt;UserDetails&gt;
                                &lt;Rid&gt;&lt;/Rid&gt;
                            &lt;/UserDetails&gt;
                        &lt;/Request&gt;'

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
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');