使用 OPENXML 在 SQL Server 中获取标记和属性的名称

Question

我必须使用以下格式处理XML文件：

<Root>
<A name="x1">
<B exp="h1" ref="r1"/>
<C exp="h2" ref="r2" rat = "ra1"/>
<D exp="h3" ref="r3"/>
</A>
<A name="x2">
<E exp="h4" ref="r4"/>
<F exp="h5" ref="r5"/>
</A>
</Root>

我想编写一个存储过程来获取如下表：

|A_name|tag_name|attrbute|val|
|x1    |   B    |exp|h1|
|x1    |   B    |ref|r1|
|x1    |   C    |exp|h2|
|x1    |   C    |rat|ra1|
|x1    |   C    |ref|r2|
|x1    |   D    |exp|h3|
|x1    |   D    |ref|r3|
|x2    |   E    |exp|h4|
|x2    |   E    |ref|r4|
|x2    |   F    |exp|h5|
|x2    |   F    |ref|r5|

我该怎么办？我已经试过了

SELECT localname
FROM OPENXML(@idoc, '/Root/A') 
WHERE localname!='A'

以获取标记名称。但是，我无法使用标签 A 的属性连接它们。

我发现使用 nodes(( 和 value(( 结合使用的性能比 OPENXML 差得多。所以即使 OPENXML 已经过时了，我仍然想使用它，因为我有 GB 的 XML 结果要读取。

Answer 1

我会惊讶地看到OPENXML在处理大量数据时优于编写良好的nodes()和value()代码。OPENXML是一个极其 RBAR 的过程，要求您为每个要处理的 XML 文档调用两个存储过程和一个 UDF。如果你的代码使用nodes()和value()性能不佳，它可能有回溯引用(即：'../'(和其他此类性能杀手。

尽管如此，这里有一些OPENXML疯狂，它们将产生您正在寻找的结果集......

declare @idoc int, @doc nvarchar(max) = N'<Root>
<A name="x1">
<B exp="h1" ref="r1"/>
<C exp="h2" ref="r2" rat="ra1"/>
<D exp="h3" ref="r3"/>
</A>
<A name="x2">
<E exp="h4" ref="r4"/>
<F exp="h5" ref="r5"/>
</A>
</Root>';
exec sp_xml_preparedocument @idoc output, @doc; 
;with rowset as (
select id, parentid, nodetype, localname, prefix, namespaceuri, datatype, prev, [text]
from openxml(@idoc, '/Root/A')
)
select A_name, tag_name, attribute, val
from rowset Router
outer apply (
select A_name_id=Rinner.id
from rowset Rinner
where Rinner.nodetype=2 and Rinner.parentid=Router.id and Rinner.localname=N'name'
) NameAttributes
outer apply (
select A_name=Rinner.[text]
from rowset Rinner
where Rinner.nodetype=3 and Rinner.parentid=NameAttributes.A_name_id and Rinner.localname=N'#text'
) NameValues
cross apply (
select tag_id=Rinner.id, tag_name=Rinner.localname
from rowset Rinner
where Rinner.nodetype=1 and Rinner.parentid=Router.id
) Children
cross apply (
select attribute_id=Rinner.id, attribute=Rinner.localname
from rowset Rinner
where Rinner.nodetype=2 and Rinner.parentid=Children.tag_id
) ChildAttributes
outer apply (
select val=Rinner.[text]
from rowset Rinner
where Rinner.nodetype=3 and Rinner.parentid=ChildAttributes.attribute_id
) ChildAttributeValues
where nodetype=1 and parentid=0
order by Router.id, Children.tag_id, ChildAttributes.attribute
exec sp_xml_removedocument @idoc;

A_name  tag_name    attribute   val
x1      B           exp         h1
x1      B           ref         r1
x1      C           exp         h2
x1      C           rat         ra1
x1      C           ref         r2
x1      D           exp         h3
x1      D           ref         r3
x2      E           exp         h4
x2      E           ref         r4
x2      F           exp         h5
x2      F           ref         r5