Select XML nodes as XML in T-SQL

Question

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!

Matt W · Accepted Answer · 2010-07-07 13:58:56Z

3

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

answered Jul 7, 2010 at 13:58

Matt W

12.7k29 gold badges141 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt W · Accepted Answer · 2010-07-07 13:46:13Z

2

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

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

answered Jul 7, 2010 at 13:46

Matt W

12.7k29 gold badges141 silver badges244 bronze badges

Collectives™ on Stack Overflow

Select XML nodes as XML in T-SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related