在不知道节点名称的情况下查询 XML 创建字段名称 [英] Query XML creating field names whithout knowing node names

问题描述

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

If I have a SQL SERVER 2012 table containing an XML field type. The records it could contain are as follows.

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

I have simplified my problem to the following.

记录 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)

For a known root value (i.e. KNOWN_NAME_1)

即如果我更新节点值(我没有)，我可以

I.e. If I new the node values (which I don't) I could

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)

一切都很好，但是我想在不知道节点名称的情况下对所有节点(数量未知)执行此操作.根将只包含它不会变得更深的节点.

All is good however I want to do this for all the nodes (unknown quantity) without knowing the node names. The root will only contain nodes it wont get any deeper.

这在 SQL 中可行吗?

Is this possible in SQL?

我已经看过这个，但我怀疑我是否可以获得足够的权利来实施它.

I have looked at this but I doubt I can get enough rights to implement it.

http://architectshack.com/ClrXmlShredder.ashx

推荐答案

如果您不知道输出中的列名，则必须使用 动态 SQL:

If you don't know the column names in the output you have to use 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

动态生成的查询如下所示:

The dynamically generated query looks like this:

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

这篇关于在不知道节点名称的情况下查询 XML 创建字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！