1

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

1 Answer 1

7

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.