SQL Server вставляет с параметром XML - пустая строка, не преобразовывающая в пустой указатель для числового
У меня есть хранимая процедура, которая берет параметр XML и вставляет узлы "Объекта" как записи в таблицу. Это хорошо работает, если одно из числовых полей не имеет значение пустой строки в XML. Затем это бросает "ошибку при преобразовании типа данных nvarchar к числовой" ошибке.
Существует ли способ для меня сказать SQL преобразовывать пустую строку в пустой указатель для тех числовых полей в коде ниже?
-- @importData XML <- stored procedure param
DECLARE @l_index INT
EXECUTE sp_xml_preparedocument @l_index OUTPUT, @importData
INSERT INTO dbo.myTable
(
[field1]
,[field2]
,[field3]
)
SELECT
[field1]
,[field2]
,[field3]
FROM OPENXML(@l_index, 'Entities/Entity', 1)
WITH
(
field1 int 'field1'
,field2 varchar(40) 'field2'
,field3 decimal(15, 2) 'field3'
)
EXECUTE sp_xml_removedocument @l_index
Править: И если это помогает, демонстрационный XML. Ошибка брошена, если я не комментирую field3 в коде выше или обеспечиваю значение в field3 ниже.
<?xml version="1.0" encoding="utf-16"?>
<Entities>
<Entity>
<field1>2435</field1>
<field2>843257-3242</field2>
<field3 />
</Entity>
</Entities>
2 ответа
Если вы будете использовать SQL Server XQuery, вы можете сделать что-то вроде этого:
SELECT
nodes.entity.value('(field1)[1]', 'int') 'Field1',
nodes.entity.value('(field2)[1]', 'varchar(50)') 'Field 2',
CAST(ISNULL(nodes.entity.value('(field3)[1]', 'varchar(50)'), '0.00')
AS DECIMAL(15,2)) 'Field 3'
FROM
@importData.nodes('/Entities/Entity') AS nodes(entity)
В основном, преобразовать значение field3 в строка, если она ПУСТО (NULL), вместо этого используйте 0.00 в качестве ДЕСЯТИЧНОГО значения.
Я не знаю, есть ли способ с подходом OPENXML сделать что-то подобное ...
ПРИМЕЧАНИЕ: я бы не стал пробовать это, но marc_s направил меня на верный путь, поэтому я отдаю ему должное за ответ.
Я попробовал XQuery, как предложил Марк, но производительность была ужасной для того количества записей, с которыми я имел дело. Но я использовал похожую технику в моем исходном коде ...
EXECUTE sp_xml_preparedocument @l_index OUTPUT, @importData
INSERT INTO dbo.myTable
(
[field1]
,[field2]
,CASE
WHEN [field3] = '' THEN NULL
ELSE CAST([wl_llr] AS DECIMAL(15,2)) -- CHANGED TO CASE
END
)
SELECT
[field1]
,[field2]
,[field3]
FROM OPENXML(@l_index, 'Entities/Entity', 1)
WITH
(
field1 int 'field1'
,field2 varchar(40) 'field2'
,field3 varchar(50) 'field3' -- CHANGED TO VARCHAR
)
EXECUTE sp_xml_removedocument @l_index