asked on
SELECT TOP 10
Employee.IndividualID ,
Employee.FirstName ,
Employee.LastName,
Phone.*
FROM Individuals AS Employee
INNER JOIN ( SELECT IndividualID AS IndividualID,
Code AS CODE,
[Description] AS [DESCRIPTION] ,
PhoneNumber AS PhoneNumber
FROM dbo.vwIndividualPhones
) Phone ON Phone.IndividualID = Employee.IndividualID
FOR XML AUTO , ROOT('Employees') , ELEMENTS;
<Employees>
<Employee>
<IndividualID>1747652</IndividualID>
<FirstName>ALICIA</FirstName>
<LastName>SMITH</LastName>
<Phone>
<IndividualID>1747652</IndividualID>
<CODE>HP</CODE>
<DESCRIPTION>Home Phone</DESCRIPTION>
<PhoneNumber>1111111111</PhoneNumber>
</Phone>
</Employee>
</Employees>
<Employees>
<Employee>
<IndividualID>1747652</IndividualID>
<FirstName>ALICIA</FirstName>
<LastName>SMITH</LastName>
<Phones>
<Phone>
<IndividualID>1747652</IndividualID>
<CODE>HP</CODE>
<DESCRIPTION>Home Phone</DESCRIPTION>
<PhoneNumber>1111111111</PhoneNumber>
</Phone>
<Phone>
<IndividualID>1747652</IndividualID>
<CODE>WP</CODE>
<DESCRIPTION>Work Phone</DESCRIPTION>
<PhoneNumber>2222222222</PhoneNumber>
</Phone>
</Phones>
</Employee>
</EMployees>