查询创建字段名称而不知道节点名称的XML

Question

如果我有一个包含XML字段类型的SQL SERVER 2012表。它可以包含的记录如下。

我已将我的问题简化为以下内容。

记录1：

ID_FIELD='nn1' 

XML_FIELD= 
<KNOWN_NAME_1> 
    <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1> 
    <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2> 
    ... Maybe more ... 
</KNOWN_NAME_1> 

记录2：

ID_FIELD='nn2' 

XML_FIELD= 
<KNOWN_NAME_2> 
    <UNKNOWN_NAME1>Some value</UNKNOWN_NAME1> 
    <UNKNOWN_NAME2>Some value</UNKNOWN_NAME2> 
    ... Maybe more unknown fields ... 
</KNOWN_NAME_2> 

我要输出非XML：

UNKNOWN_NAME1 | UNKNOWN_NAME2 | ETC 
----------------------------------- 
Some Value  Some value 

对于已知的根值（即KNOWN_NAME_1）

即，

也就是说如果我新的节点值（我没有），我可以

SELECT 
    XMLData.Node.value('UNKNOWN_NAME1[1]', 'varchar(100)') , 
    XMLData.Node.value('UNKNOWN_NAME2[1], 'varchar(100)') 
FROM FooTable 
    CROSS APPLY MyXmlField.nodes('//KNOWN_NAME_1') XMLData(Node) 
    -- WHERE SOME ID value = 'NN1' (all XML records have a separate id) 

一切都很好，但是我想对所有节点（未知数）做到这一点不知道节点名称。根将只包含它不会得到更深的节点。

这是可能的SQL？

我看过这个，但我怀疑我可以获得足够的权利来实现它。

http://architectshack.com/ClrXmlShredder.ashx

Answer 1

如果你不知道在输出中列名称必须使用dynamic SQL：

-- Source table 
declare @FooTable table 
(
    ID_FIELD char(3), 
    XML_FIELD xml 
) 

-- Sample data 
insert into @FooTable values 
('nn1', '<KNOWN_NAME_1> 
      <UNKNOWN_NAME1>Some value1</UNKNOWN_NAME1> 
      <UNKNOWN_NAME2>Some value2</UNKNOWN_NAME2> 
     </KNOWN_NAME_1>') 

-- ID to look for 
declare @ID char(3) = 'nn1' 

-- Element name to look for 
declare @KnownName varchar(100) = 'KNOWN_NAME_1' 

-- Variable to hold the XML to process 
declare @XML xml 

-- Get the XML 
select @XML = XML_FIELD 
from @FooTable 
where ID_FIELD = @ID 

-- Variable for dynamic SQL 
declare @SQL nvarchar(max) 

-- Build the query 
select @SQL = 'select '+stuff(
    (
    select ',T.N.value('''+T.N.value('local-name(.)', 'sysname')+'[1]'', ''varchar(max)'') as '+T.N.value('local-name(.)', 'sysname') 
    from @XML.nodes('/*[local-name(.)=sql:variable("@KnownName")]/*') as T(N) 
    for xml path(''), type 
).value('.', 'nvarchar(max)'), 1, 1, '')+ 
    ' from @XML.nodes(''/*[local-name(.)=sql:variable("@KnownName")]'') as T(N)' 

-- Execute the query 
exec sp_executesql @SQL, 
    N'@XML xml, @KnownName varchar(100)', 
    @XML = @XML, 
    @KnownName = @KnownName 

结果：

UNKNOWN_NAME1 UNKNOWN_NAME2 
--------------- --------------- 
Some value1  Some value2 

动态生成的查询看起来是这样的：

select T.N.value('UNKNOWN_NAME1[1]', 'varchar(max)') as UNKNOWN_NAME1, 
     T.N.value('UNKNOWN_NAME2[1]', 'varchar(max)') as UNKNOWN_NAME2 
from @XML.nodes('/*[local-name(.)=sql:variable("@KnownName")]') as T(N) 

SE-Data