将SQL Server 2008表列转换为XML并聚合xpath值

I've got a table with xml stored in NVARCHAR. It's just like that.

我有一个存储在NVARCHAR中的xml表。就是这样。

The xml looks like

xml看起来像

<Container>
  <Data>
    <SomeNode>val1</SomeNode>
  </Data>
  <Data>
    <SomeNode>val2</SomeNode>
  </Data>
</Container>

basically I need a to query all val1...valX from all the xml strings in table.

基本上我需要从表中的所有xml字符串查询所有val1 ... valX。

So far I've managed to come up with

到目前为止,我已经设法提出了

SELECT CAST(XmlColumn AS XML).query('//SomeNode') FROM ThatTableWithXmlInStrings

which gives me list of lists of nodes. And here I'm lost: how do I flatten that into single values?

这给了我节点列表的列表。在这里,我迷失了:我如何将其扁平化为单一价值观?

2 个解决方案

#1

select T2.X.value('text()[1]', 'nvarchar(10)')
from ThatTableWithXmlInStrings
  cross apply (select cast(XmlColumn as xml)) T1(X)
  cross apply T1.X.nodes('/Container/Data/SomeNode') as T2(X)

#2

There are two methods:

有两种方法:

add text() function to the xquery

将text()函数添加到xquery

use nodes method link

使用节点方法链接

#1

select T2.X.value('text()[1]', 'nvarchar(10)')
from ThatTableWithXmlInStrings
  cross apply (select cast(XmlColumn as xml)) T1(X)
  cross apply T1.X.nodes('/Container/Data/SomeNode') as T2(X)

#2