Neat XML from T-SQL

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 be开发者_开发问答st 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?

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.

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;