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
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');
  • you h've done it. thanks the elegant solution.Marked as answer – Deeptechtons Apr 1 '11 at 4:23
  • could the above be modified to return a structure as below '<UserDetails> <Account> <UserName></UserName> <Hash></Hash> </Account> <Personals> <Personal><Phone></Phone><City></City></Personal> <Personal><Phone></Phone><City></City></Personal> </Personals> </UserDetails>' where in single user might have more than 1 personal details. – Deeptechtons Apr 1 '11 at 5:19
  • @Deeptechtons Please see my edit and let me know if it works. – Matt M Apr 1 '11 at 13:14
  • you have done it again voted up. – Deeptechtons Apr 2 '11 at 4:21
  • wooh wooh wooh, sorry for bumping again but i get recursive entries when i wanted all the entries and not just by ID. How do i distinct that because it says it cannot apply distinct on xml types and also Each GROUP BY expression must contain at least one column that is not an outer reference. – Deeptechtons Apr 2 '11 at 5:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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