Outputting as XML

• neddyflanders

  SSC Eights!

  Points: 972

  More actions

  February 19, 2003 at 8:52 am

  #81507

  I have a table called TempTable. It has three values in it. PeopleCode, FirstName & LastName. I am trying to create a text file that looks like the following (put arrows in to indent):

  <Employee>

  <PeopleCode>3</PeopleCode>

  ---> <FirstName>John</FirstName>

  ---> <LastName>Doe</LastName>

  <PeopleCode>4</PeopleCode>

  ---> <FirstName>Harry</FirstName>

  ---> <LastName>Who</LastName>

  </Employee>

  Using this code:

  Select FirstName, LastName, PeopleCode

  FROM TempTable As Employee

  For XML Auto, Elements

  I get close but I get the following result, as you can see Employee is not the root for all the values. Any help would be appreciated:

  <Employee>

  <FirstName>Thomas</FirstName>

  <LastName>Jefferson</LastName>

  <PeopleCode>008</PeopleCode>

  </Employee>

  <Employee>

  <FirstName>George</FirstName>

  <LastName>Washington</LastName>

  <PeopleCode>009</PeopleCode>

  </Employee>

• cppwiz

  SSCarpal Tunnel

  Points: 4036

  More actions

  February 20, 2003 at 1:36 pm

  #449422

  Since you want to change the shape of your XML document, you need to use FOR XML EXPLICIT.

  Here's an article about the different FOR XML clauses:

  http://qa.sqlservercentral.com/columnists/lplatt/article4_1.asp