Select XML nodes as XML in T-SQL

Question

This one seems so simple, but something is missing for me ...

Given this SQL:

declare @xml XML set @xml = '<people> <person> <name>Matt</name> <surname>Smith</surname> <person> <person> <name>John</name> <surname>Doe</surname> <person> </people>' 

How would you like to get a table containing:

 people ---------------------------------------------------------------------- <person>\n <name>Matt</name>\n <surname>Smith</surname>\n <person> <person>\n <name>John</name>\n <surname>Doe</surname>\n <person> 

i.e.: capturing entire nodes as nvarchar (NNN) elements, and not just their names, attributes or values?

I tried using node (), text (), fn: node (), fn: text (), blah blah, etc. Nuffin yet!

Answer 1

Also, if anyone is interested, add an extension to the query that returns only the root children of the nodes, like xml if they themselves have children:

 SELECT pref.query('.') as XmlExtract FROM @xml.nodes('/*/*') AS extract(pref) WHERE pref.value('./*[1]', 'nvarchar(10)') IS NOT NULL 

Answer 2

Screams, I think I answered my question again ...

 SELECT pref.query('.') as PersonSkills FROM @xml.nodes('/*/*') AS People(pref)