在 SQL Server 的 OPENXML 函数中将空元素视为 null

Question

我有以下（高度简化的）XML 文档，我正在使用 OPENXML 函数将其读入数据库：

<root>
    <row>
        <bar>123</bar>
    </row>
    <row>
        <bar>0</bar>
    </row>
    <row>
        <bar></bar>
    </row>
</root>

我像这样导入到数据库中：

insert into [Foo]
    ([bar])
select
    ds.[bar]
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] int) ds

问题是 OPENXML 将 int 数据类型的空字段转换为零，所以这插入到我的表中：

bar
----
123
0
0

我想要插入到表中的是：

bar
----
123
0
NULL

如何让 OPENXML 函数将空字段视为 NULL，并且默认情况下不将其转换为零？

Answer 1

由于没有人有任何想法，这就是我“解决”它的方法，尽管对我来说这似乎是一种黑客攻击：

insert into [Foo]
    ([bar])
select
    isnull(ds.[bar], '') when '' then null else CAST(ds.[bar] as int) end
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] nvarchar(20)) ds

Answer 2

刚刚遇到了类似的问题，并使用 SQL 中的 NULLIF 函数解决了它。

MSDN 上的 NULLIF

我相信你也会忽略它:)

insert into [Foo]
    ([bar])
select
    NULLIF(ds.[bar], '')
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] nvarchar(20)) ds

摆脱由 CASE...END 语句造成的混乱。

希望有帮助！

Answer 3

不确定您的 xml 有多“高度简化”，但如果您有一个指定 null 的属性，您也可以执行类似于答案的操作 此处；基本上：

<root>
<row>
    <bar>123</bar>
</row>
<row>
    <bar>0</bar>
</row>
<row>
    <bar nil="true"></bar>
</row>

select ds.bar
from openxml(@xmlHandle, 'root/row', 2) with (
  [bar] nvarchar(20) 'bar[not(@nil = "true")]'
) ds

这个场景对我有用

Answer 4

我最初使用 NULLIF 作为已经建议的，但我在WITH模式中找到了另一个选项：

SELECT bar
FROM openxml(@xmlHandle, 'root/row', 2)
WITH (
  [bar] nvarchar(20) 'bar[string-length(.)>0]'
)

我正在查看数百列和数千行，所以大小不错，但不是很大。我发现性能与 this 或 NULLIF 基本相同，但我喜欢将定义保留在 WITH 子句中，以便 SELECT 可以保持干净（因为我倾向于在那里发生很多其他事情）。

青年MMV

Answer 5

如果完全省略 Bar 元素，您将得到所需的结果。

<root>
    <row>
        <bar>123</bar>
    </row>
    <row>
        <bar>0</bar>
     </row>
     <row>
     </row>
</root>

然后

select
    ds.[bar]
from openxml(@xmlHandle, 'root/row', 2)
with ([bar] int) ds

结果是

bar
----
123
0
NULL