开发者

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: Grabbi开发者_如何学Cng 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!


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


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

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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜