Select XML nodes as XML in T-SQL

This one seems so simple, but I must be missing something...

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 go about getting 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>

ie: Grabbing entire nodes as nvarchar(NNN) elements, not just their names, attributes or values?

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

Answers


Crikey, I think I've answered my own question again...

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

Further, if anyone is interested, here's an extension to the query which only returns the root node's immediate child nodes, as xml, if they have child nodes themselves:

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

Need Your Help

Footer font size too big in internet explorer

html css internet-explorer firefox

I am having a problem developing a web page, http://prime-mmorpg.tk/templetetest.php is the page, if you look at the footer in IE, it is 20 times bigger then it should be, like it is in FireFox. Wh...

curl certificate Error_ssl.c334: No root certificate specified for verification of other side certificate

ssl curl

"""You also need CA certificates bundle file for SSL support. Download cacert.pem from the cURL site, rename it to curl-ca-bundle.crt, and place in the directory where you make installer, or in any