Avatar of Larry Brister
Larry Brister
Flag for United States of America

asked on 

SQL Server XML Select sub tables

In my following code I am getting ONLY the top phone number for the individuals from the Phone.*

This...
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; 

Open in new window


Gets me this...
<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>

Open in new window


When I want this...

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

Open in new window

XMLMicrosoft SQL Server

Avatar of undefined
Last Comment
Larry Brister

8/22/2022 - Mon