We help IT Professionals succeed at work.

MS SQL Server XML Output

Larry Brister
on
202 Views
Last Modified: 2018-03-21
In my select below what I would like to produce is this XML
<Individuals>
  <Individual>
    <IndividualID> 123</IndividualID>
    <FirstName>Larry</FirstName>
    <LastName>Brister</LastName>
    <Phones>
      <Phone>
        <code>FAX</code>
        <phonenumber>1234569874</phonenumber>
      </Phone>
      <Phone>
        <code>WRK</code>
        <phonenumber>9541234567</phonenumber>
      </Phone>
    </Phones>
  </Individual>
  <Individual>
    <IndividualID>124</IndividualID>
    <FirstName>Bob</FirstName>
    <LastName>Carrr</LastName>
    <Phones>
      <Phone>
        <code>FAX</code>
        <phonenumber>1234599874</phonenumber>
      </Phone>
      <Phone>
        <code>WRK</code>
        <phonenumber>2222222222</phonenumber>
      </Phone>
    </Phones>
  </Individual>
</Individuals>

Open in new window




SELECT  i.IndividualID ,
        i.FirstName ,
        i.LastName ,
        e.EmailAddress ,
        pp.Code ,
        pp.PhoneNumber
FROM    Individuals i
        JOIN dbo.IndividualEmails e ON i.IndividualID = e.IndividualID
        JOIN ( SELECT   i.IndividualID ,
                        i.PhoneNumber ,
                        t.Code
               FROM     IndividualPhoneNumbers i
                        JOIN dbo.PhoneNumberTypes t ON t.PhoneNumberTypeID = i.PhoneNumberTypeID
             ) pp ON i.IndividualID = pp.IndividualID;

Open in new window

Comment
Watch Question

zc2
CERTIFIED EXPERT

Commented:
I would do that in two steps. First, creating a simple XML by adding "for XML auto" to your SQL expression.
Then use XSLT to transform it to whatever schema you like.
Larry Bristersr. Developer

Author

Commented:
zc2
I prefer to do everything in SQL Server
Larry Bristersr. Developer

Author

Commented:
I am getting xml this way...
But I need all of the Phones wrapped in its own <Phones> element


SELECT TOP 1
        Employee.IndividualID ,
        Employee.FirstName ,
        Employee.LastName ,
        (SELECT Phone.Code ,
        Phone.[Description] ,
        Phone.PhoneNumber FROM dbo.vwIndividualPhones Phone WHERE Phone.IndividualID = Employee.IndividualID
		FOR XML AUTO, TYPE, ELEMENTS)
FROM    Individuals AS Employee
        INNER JOIN dbo.vwIndividualPhones Phone ON Phone.IndividualID = Employee.IndividualID
FOR     XML AUTO , ROOT('Employees') , ELEMENTS; 

Open in new window


This is the output...
<Employees>
  <Employee>
    <IndividualID>1747652</IndividualID>
    <FirstName>ALICIA</FirstName>
    <LastName>GOODGIRL</LastName>
    <Phone>
      <Code>AP</Code>
      <Description>Alt Phone</Description>
      <PhoneNumber>0</PhoneNumber>
    </Phone>
    <Phone>
      <Code>FAX</Code>
      <Description>Fax</Description>
      <PhoneNumber>0</PhoneNumber>
    </Phone>
    <Phone>
      <Code>CP</Code>
      <Description>Cell Phone</Description>
      <PhoneNumber>1234123412</PhoneNumber>
    </Phone>
    <Phone>
      <Code>HP</Code>
      <Description>Home Phone</Description>
      <PhoneNumber>9999999999</PhoneNumber>
    </Phone>
    <Phone>
      <Code>CP</Code>
      <Description>Cell Phone</Description>
      <PhoneNumber>8888888888</PhoneNumber>
    </Phone>
    <Phone>
      <Code>WP</Code>
      <Description>Work Phone</Description>
      <PhoneNumber>7777777777</PhoneNumber>
    </Phone>
  </Employee>
</Employees>

Open in new window

CERTIFIED EXPERT

Commented:
do you still need assistance here?

can you provide your source table structures?
Larry Bristersr. Developer

Author

Commented:
I'll provide as soon as I return to my desk
Larry Bristersr. Developer

Author

Commented:
The vwPhones is essentially three columns
id
typeid
and the PhoneNumber
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions