在T-SQL中选择XML节点作为XML

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

这个看起来很简单,但我必须遗漏一些东西......

Given this SQL:

鉴于此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?

即:将整个节点作为nvarchar(NNN)元素抓取,而不仅仅是它们的名称,属性或值?

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

我已经尝试过使用node(),text(),fn:node(),fn:text(),等等等等......还在努力!

2 个解决方案

#1

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

Crikey,我想我已经回答了我自己的问题......

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

#2

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:

此外,如果有人感兴趣,这里是查询的扩展,它只返回根节点的直接子节点,如果它们自己有子节点,则返回xml:

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

#1