在SQL Server中从XML中选择空值

I'm trying to select from XML that has a null as one of the attributes. Instead of returning a null, it returns a 0. What am I doing wrong?
See code below to replicate:

我试图从XML中选择一个属性为null的值。它不是返回null，而是返回0。我做错了什么?参见下面的代码进行复制:

declare @a xml
select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace">
  <Element>
    <Property1>1</Property1>
    <Property2>1</Property2>
  </Element>
  <Element>
    <Property1 xsi:nil="true" />
    <Property2>2</Property2>
  </Element>
  <Element>
    <Property1>3</Property1>
    <Property2>3</Property2>
  </Element>
</TestSet>'

 select ParamValues.TaskChainerTask.query('Property1').value('.','int') as Property1,
        ParamValues.TaskChainerTask.query('Property2').value('.','int') as Property2
   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

returns:

Property1   Property2
1           1
0           2
3           3

This returns the same thing:

它返回相同的东西:

declare @a xml
select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace">
  <Element>
    <Property1>1</Property1>
    <Property2>1</Property2>
  </Element>
  <Element>
    <Property1 xsi:nil="true" />
    <Property2>2</Property2>
  </Element>
  <Element>
    <Property1>3</Property1>
    <Property2>3</Property2>
  </Element>
</TestSet>'

 select ParamValues.TaskChainerTask.query('Property1').value('.','int') as Property1,
        ParamValues.TaskChainerTask.query('Property2').value('.','int') as Property2
   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

Thanks in advance.

提前谢谢。

7 个解决方案

#1

I think if you use the number() function, you'll get null as expected. This only works for number types though:

我认为如果使用number()函数，就会像预期的那样得到null。这只适用于数字类型:

select 
   ParamValues.TaskChainerTask.query('Property1').value('number(.)','int') as Property1,         
   ParamValues.TaskChainerTask.query('Property2').value('number(.)','int') as Property2
from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

#2

http://go4answers.webhost4life.com/Example/including-null-columns-empty-elements-125474.aspx

http://go4answers.webhost4life.com/example/including - null列- 125474. aspx -空元素

[not(@xsi:nil = "true")]

This will select null. By the way author code has a typo

这将选择零。顺便说一下，作者代码有一个输入错误。

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace"

instance is misspelled as instace

实例被拼错为instace

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

Working version of author code

作者代码的工作版本

declare @a xml
            select @a = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <Element>
                <Property1>1</Property1>
                <Property2>1</Property2>
              </Element>
              <Element>
                <Property1 xsi:nil="true" />
                <Property2>2</Property2>
              </Element>
              <Element>
                <Property1>3</Property1>
                <Property2>3</Property2>
              </Element>
            </TestSet>'

             select ParamValues.TaskChainerTask.value('./Property1[1][not(@xsi:nil = "true")]','int') as Property1,
                    ParamValues.TaskChainerTask.value('./Property2[1][not(@xsi:nil = "true")]','int') as Property2
               from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

#3

Because you are setting the fields to INT you have the problem that both xsi:nil="true" fields and the value 0 will end up as 0 as the default value for INT Is 0.

因为你将字段设置为INT类型你会遇到这样的问题xsi:nil="true"字段值0最终会变成0，因为INT的默认值是0。

You could convert to VARCHAR first to detect the empty string ('') that string fields containing xsi:nil="true" produce and then convert the result to INT.

您可以先转换到VARCHAR，以检测包含xsi:nil="true"的字符串字段的空字符串(")，然后将结果转换为INT。

This SELECT will give you the answer you are after

这个选择将给出您所要的答案

SELECT  CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property1').value('.', 'varchar(5)'),'')) AS Property1
      , CONVERT(INT,NULLIF(ParamValues.TaskChainerTask.query('Property2').value('.', 'varchar(5)'),'')) AS Property2
FROM    @a.nodes('(/TestSet/Element)') AS ParamValues (TaskChainerTask)

The result of this gives you:

结果是:

Property1   Property2
1           1
NULL        2
3           3

#4

I've simply used NULLIF to turn empty strings into NULLs as needed.

我只是使用NULLIF在需要时将空字符串转换为空字符串。

You can generate nil now using FOR XML, but I've never worked out how to parse it sorry...

您现在可以为XML生成nil，但我从来没有想过如何解析它，对不起……

#5

I would sugest this approach:

我建议这样做:

DECLARE @a XML = '<TestSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instace">
  <Element>
    <Property1>1</Property1>
    <Property2>1</Property2>
  </Element>
  <Element>
    <Property1 xsi:nil="true" />
    <Property2>2</Property2>
  </Element>
  <Element>
    <Property1>3</Property1>
    <Property2>3</Property2>
  </Element>
</TestSet>'

SELECT
    ParamValues.TaskChainerTask
        .value('./Property1[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property1,
    ParamValues.TaskChainerTask
        .value('./Property2[not(./@*[local-name()="nil"] = "true")][1]', 'int') as Property2
FROM @a.nodes('//Element') ParamValues(TaskChainerTask)

#6

A clever way of doing this would be to remove the Property1 node from the XML where null values are desired. So what ever node you want to be null in your result set just do not add it to the XML. In this way you will not have to add the xsi:nill attribute as well.

这样做的一个聪明方法是将Property1节点从需要空值的XML中删除。因此，在结果集中希望为null的任何节点都不要将其添加到XML中。这样，您就不必添加xsi:nill属性。

So Below will also result in a null:

下面也会得到一个null

declare @a xml
select @a = '<TestSet>
  <Element>
    <Property1>1</Property1>
    <Property2>1</Property2>
  </Element>
  <Element>
     //I have removed the property1 node and this will result in a null value
    <Property2>2</Property2>
  </Element>
  <Element>
    <Property1>3</Property1>
    <Property2>3</Property2>
  </Element>
</TestSet>'

 select ParamValues.TaskChainerTask.value('./Property1[1]','int') as Property1,
        ParamValues.TaskChainerTask.value('./Property2[1]','int') as Property2
   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

It can be seen in the above example that there is no Property1 node hence it will result in a null value

可以在上面的示例中看到，没有Property1节点，因此它将导致一个空值

#7

I'm not sure if your particular case requires you to sub-query the nodes first, but if not you can request the .value and provide the xPath. Since the Property1 node exists, you want to evaluate the text() of the Property1 node and not the node itself:

我不确定您的特定情况是否要求您首先对节点进行子查询，但如果不是，您可以请求.value并提供xPath。由于Property1节点存在，您希望计算Property1节点的text()，而不是节点本身:

 select  ParamValues.TaskChainerTask.value('Property1[1]/text()[1]','int') as Property1,
        ParamValues.TaskChainerTask.value('Property2[1]/text()[1]','int') as Property2

   from @a.nodes('(/TestSet/Element)') as ParamValues(TaskChainerTask)

In addition to make sure this works in other cases you can provide the most detailed element path in the @a.nodes xPath statment and walk up with "../" instead of sub-querying the node results.

除此之外，在其他情况下，您可以在@a中提供最详细的元素路径。节点xPath语句并执行“.. .”/“而不是子查询节点结果。

#1