Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi could someone provide me some clues or solution to retrive 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 fields,

Rid,UserName,Hash

Table2 fields

Rid,Phone,City,Email

table1 and table2 are connected via the Rid field. I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in sql express 2005. 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 Storedprocedure 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
share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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');
share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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