Neat XML from T-SQL

Question

Lets say we have a table "Names":

ID    Name    Surname
1     Matt    Smith
2     John    Doe

How would you write some SQLXML to generate this:

<people>
  <person>
    <name>Matt</name>
    <surname>Smith</surname>
  <person>
  <person>
    <name>John</name>
    <surname>Doe</surname>
  <person>
</people>

The best I've got is this:

select r.value('Name[1]', 'nvarchar(10)'), r.value('Surname[1]', 'nvarchar(10)')
from Names
for xml path('people')

Which gives me:

<people>
  <name>Matt</name>
  <surname>Smith</surname>
</people>
<people>
  <name>John</name>
  <surname>Doe</surname>
</people>

In short, how do I wrap the whole lot?

Answer 1

If you want to do this an all xml way,

You can have a Variable,

Declare @XMLOP xml
SET @XMLOP  = '<people></people>'

set @XMLOP.modify('       
insert (select r.value('Name[1]', 'nvarchar(10)'), r.value('Surname[1]', 'nvarchar(10)') 
        from Names 
        for xml path('Person'))
after (/people)[1]       
');

SELECT @XMLOP;

Answer 2

Try this:

SELECT 
   Name,
   Surname
FROM 
   dbo.Names
FOR XML PATH('person'), ROOT('people')

The FOR XML PATH defines the tag to surround each individual row, while the FOR XML ... ROOT defines the root element surrounding the collection of rows.