Outputting XML from table(Using XML AUTO)

• neddyflanders

  SSC Eights!

  Points: 972

  More actions

  February 19, 2003 at 11:15 am

  #101386

  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>

• February 24, 2003 at 12:00 pm

  This was removed by the editor as SPAM

• gregj

  Old Hand

  Points: 346

  More actions

  February 24, 2003 at 2:06 pm

  #449634

  This is closer...

  select distinct

  t1.PeopleCode

  t2.FirstName,

  t2.LastName

  from TempTable as t1

  join TempTable as t2

  on t2.PeopleCode = t1.PeopleCode

  for xml auto, elements