XQuery в SQL-сервере делает SUM с нулевым значением

Я пытаюсь извлечь денежные суммы, хранящиеся в некоторых слабо сформированных столбцах xml (нет схемы, определенной для столбца XML, который, как я полагаю, является частью проблемы). Я получаю ошибку преобразования всякий раз, когда сталкиваюсь с node с 0 в качестве значения.

Пример:

select xml.value('sum(/List/value)', 'numeric') sum
from (select cast('<List><value>1</value><value>2</value></List>' as xml) xml) a

дает сумму 3, а:

select xml.value('sum(/List/value)', 'numeric') sum
from (select cast('<List><value>0</value><value>0</value></List>' as xml) xml) a

вызывает ошибку: "Ошибка преобразования типа данных nvarchar в числовой".

Любая идея, как я могу заставить мой запрос возвращать 0 при суммировании списка нулевых узлов?

Ответы

Ответ 1

Ваш комментарий предлагает ответ на вашу проблему.

Вместо преобразования в числовое значение преобразуется в float. Научная нотация преобразуется в float.

Ответ 2

вы также можете использовать и, если это утверждение выглядит следующим образом:

@x.value('if (sum(/List/value) = 0) then 0 else sum(/List/value)', 'numeric')

Ответ 3

Я наткнулся на этот вопрос и, в конечном счете, ответил, глядя на подобную проблему с целыми числами. Несмотря на задержку со времени последнего ответа, я добавляю здесь, если это поможет кому-то еще в будущем.

Сначала ваш основной ответ:

select xml.value('xs:decimal(sum(/List/value))', 'numeric') sum
from (select cast('<List><value>0</value><value>0</value></List>' as xml) xml) a

В XQuery вы можете присвоить значение стандарту XML Schema, который будет корректно обрабатываться SQL Server.

ПОЖАЛУЙСТА, ОБРАТИТЕ ВНИМАНИЕ: "Числовые" по умолчанию в SQL Server не имеют десятичных знаков (масштаб "0" )! Вероятно, вы намеревались сделать что-то большее:

select xml.value('xs:decimal(sum(/List/value))', 'numeric(20,5))') sum
from (select cast('<List><value>0</value><value>0</value></List>' as xml) xml) a

(вы не можете заставить SQL Server выводить точность или масштаб из значения, возвращаемого из Xml, вы должны явно указать его)

Наконец, фактическая проблема, которую я лично нуждалась в решении, была почти такой же, за исключением того, что я имел дело с целыми числами, которые также противоречат представлению xml значений "0" double:

select xml.value('xs:int(sum(/List/value))', 'int') sum
from (select cast('<List><value>0</value><value>0</value></List>' as xml) xml) a

ОБНОВЛЕНИЕ: Проблема с решением для обработки десятичных данных, которое я опубликовал выше (преобразование в десятичный код в XQuery до того, как SQL получает синтаксический анализ значения) состоит в том, что агрегация фактически происходит с (предполагаемым/предполагаемым) с плавающей точкой (двойной). Если значения, хранящиеся в вашем Xml, требуют высокой степени точности, это может быть неправильной задачей - агрегация с плавающей запятой может фактически привести к потере данных. Здесь мы теряем последнюю цифру числа, которое мы суммируем:

select xml.value('xs:decimal(sum(/List/value))', 'numeric(28, 0)') sum
from (select cast('<List>
        <value>1000000000000000000000000001</value>
        <value>1000000000000000000000000001</value>
    </List>' as xml) xml) a

(выходит на "20000000000000000000000000000000", что неверно)

Эта проблема одинаково применима к другим подходам, предлагаемым здесь, таким как явно чтение значения как "float" в T-SQL.

Чтобы избежать этого, в качестве окончательной опции используется выражение XQuery FLWOR для установки типа данных перед операцией агрегирования. В этом случае агрегация происходит правильно, и мы имеем правильное суммарное значение (при этом также обрабатываем значения "0" , если/когда они возникают):

select xml.value('sum(for $r in /List/value return xs:decimal($r))', 'numeric(28, 0)') sum
from (select cast('<List>
        <value>1000000000000000000000000001</value>
        <value>1000000000000000000000000001</value>
    </List>' as xml) xml) a

(отображается значение "20000000000000000000000000000002", правильное значение)