CODEWITHSUNDEEP
sqlsql-serverxmlsql-server-2005
Matt W
Matt W

Reputation: 12423

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!

Upvotes: 4

Views: 4082

Answers (2)

Matt W
Matt W

Reputation: 12423

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

Upvotes: 3

Matt W
Matt W

Reputation: 12423

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

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

Upvotes: 2

Related Questions