SQL - Formatting XML response value

Go To StackoverFlow.com


I want to receive my response XML in following format...


I have 2 different select statements here...

SELECT name, age FROM Person_Details
WHERE id = 12

SELECT Contact_Type, Contact_Value FROM Person_Contact_Details
Where id = 12

Any suggestions here...I tried with combinations of FOR XML EXPLICIT/PATH.

2009-06-16 08:03
by Vijay
what's your DBMS? MSSQL? MySQL? Possible duplicate: http://stackoverflow.com/questions/999513/what-is-the-equivalent-of-for-xml-auto-in-mysql-for-having-an-xml-string-that/999531#99953 - Jonathan Fingland 2009-06-16 08:19



SELECT name AS Name
            , age AS Age
            , (SELECT Contact_Type AS ContactType
                , Contact_Value AS ContactValue
                FROM Person_Contact_Details c
                WHERE c.id = p.id
                FOR XML PATH('ContactDetail'), TYPE) AS ContactDetails
        FROM Person_Details p
        WHERE id = 12
        FOR XML PATH('PersonDetails')
2009-06-16 08:19
by Remus Rusanu
Thanks a lot...This fixed my query.. - Vijay 2009-06-16 10:14