如何在SQL Server 2008中从XML文档中选择每个标记?

I'm looking if it is possible to make query in T-SQL that will select value from every tag of my XML document.

我正在寻找是否有可能在T-SQL中进行查询，该查询将从我的XML文档的每个标记中选择值。

I don't know how many tags there can be, and i don't know their names, because it should be dynamic... It is not important format of output (it can be manipulated later), only thing that is matter is that I get data from every tag :)

我不知道可以有多少个标签，也不知道它们的名字，因为它应该是动态的……它不是重要的输出格式(它可以稍后进行操作)，唯一重要的是我从每个标记中获取数据:)

Thanks

谢谢

3 个解决方案

#1

You can try something like this:

你可以试试这样的方法:

DECLARE @input XML = '..... add your XML here........'

SELECT 
    NodeName = Nod.value('local-name(.)', 'varchar(50)'),
    NodeValue = Nod.value('.', 'varchar(50)')
FROM @input.nodes('//*') AS TBL(Nod)

This will list all nodes - name and value - in your XML.

这将列出XML中的所有节点——名称和值。

WARNING: I just picked varchar(50) at random here, as the datatype for the XML node element. If that's not suitable for you - adapt as needed! Since you're converting them all at once, you have to convert them all to the same datatype - and varchar seems like a fairly safe choice :-)

警告:我只是随机选择varchar(50)作为XML节点元素的数据类型。如果那不适合你-适应需要!由于您要同时转换它们，您必须将它们全部转换为相同的数据类型—而varchar似乎是一个相当安全的选择:

#2

declare @xml xml

select 
    x.n.query('local-name(.)'),
    x.n.value('(text())[1]','varchar(100)')
from @xml.nodes('//.') x(n)

#3

You can use openxml to get an edge table. It will give you node values as well as attribute values. If you have complex XML with a mix of nodes and text you will get the values split in separate rows.

可以使用openxml获取边缘表。它将提供节点值和属性值。如果您有复杂的XML，其中包含节点和文本，则会将值分割为不同的行。

declare @xml xml

set @xml = 
'<root Attrib="RootAttribute">
  <item>Value item 1</item>
  <item>
    Value item 2
      <subitem>sub value in 2</subitem>
    More text in item 2
  </item>
 </root>'

declare @idoc int
exec sp_xml_preparedocument @idoc out, @xml

select *
from openxml(@idoc, '')

exec sp_xml_removedocument @idoc

Result:

结果:

id parentid nodetype localname prefix namespaceuri datatype prev text
0  NULL     1        root      NULL   NULL         NULL     NULL NULL
2  0        2        Attrib    NULL   NULL         NULL     NULL NULL
8  2        3        #text     NULL   NULL         NULL     NULL RootAttribute
3  0        1        item      NULL   NULL         NULL     NULL NULL
9  3        3        #text     NULL   NULL         NULL     NULL Value item 1
4  0        1        item      NULL   NULL         NULL     3    NULL
6  4        3        #text     NULL   NULL         NULL     NULL Value item 2
5  4        1        subitem   NULL   NULL         NULL     6    NULL
10 5        3        #text     NULL   NULL         NULL     NULL sub value in 2
7  4        3        #text     NULL   NULL         NULL     5    More text in item 2

#1