SQL - Formatting XML response value

Question

Hi

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

<PersonDetails>
    <Name>Ajay</Name>
    <Age>29</Age>
    <ContactDetails>
     <ContactDetail>
      <ContactType>Mobile</ContactType>
      <ContactValue>9565649556</ContactValue>
     </ContactDetail>
     <ContactDetail>
      <ContactType>Email</ContactType>
      <ContactValue>[email protected]</ContactValue>
     </ContactDetail>
    </ContactDetails>
</PersonDetails>

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.

Answer 1

PATH:

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

Answer 2

how to read xml response using in sql