Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Здравствуйте!

При импорте из XML способом

SELECT ....
INTO #Table
FROM OPENXML(@idoc, '/ZZZZZ/PPPP', 2)
WITH (cccc int '../SSSS/CCCC',
nnnn int 'NNNN',
pppp bit 'PPPP',

пустым тегам вроде <NNNN/> присваиваются пустые значения в зависимости от типа поля, особенно неприятно, что числовые поля в этом случае принимают значение 0.
Подскажите, пожалуйста, можно ли присваивать полям для пустых тэгов значение NULL?
17 янв 17, 17:12    [20115374]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
declare @h int, @x xml = '<doc><data><a>1</a><b>2</b></data><data><a>1</a><b></b></data></doc>';

exec sp_xml_preparedocument @h output, @x;

select
 a, b, c
from
 openxml(@h, '/doc/data', 2)
with
(
 a int 'a',
 b int 'b[text() != ""]',
 c int 'c'
);

exec sp_xml_removedocument @h;
17 янв 17, 18:05    [20115617]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Большое-пребольшое спасибо!
18 янв 17, 09:21    [20117105]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Добрый день!

Появилась ещё одна неприятная вещь. Нехорошие люди прислали в тэге типа numeric(5,2) значение 8,75 (вместо точки - запятая). В результате получается ошибка преобразования nvarchar в numeric.
Скажите, пожалуйста, можно ли при импорте XML сделать в строке
ed numeric(5, 2) 'ED[text() != ""]'
какие-либо ещё операции, например, замену символа.
14 фев 17, 10:54    [20209985]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8350
ArkadyL,

верните файл этим нехорошим людям. Валидацию он не прошёл.
14 фев 17, 11:14    [20210057]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ArkadyL
В результате получается ошибка преобразования nvarchar в numeric.
Оставляйте как текст. Преобразовывать в число будете уже в запросе со всеми необходимыми коррекциями.
14 фев 17, 11:19    [20210077]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
В таком случае, возможно ли программно определить, в каком теге произошла ошибка? Сейчас я получаю общую ошибку и вынужден исследовать гигантский файл глазами, чтобы понять причину ошибки и по чьей вине она произошла.
14 фев 17, 11:23    [20210093]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ArkadyL
В таком случае, возможно ли программно определить, в каком теге произошла ошибка?
Зачем? Столбец, который необходимо конвертировать всегда известен.
14 фев 17, 11:25    [20210101]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Тогда надо отказаться от всех типов полей, кроме текстового. Не хочется сразу лишаться всех прелестей обработки одной командой.
14 фев 17, 11:28    [20210111]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Надо же исключить такое и в других подобных полях.
14 фев 17, 11:29    [20210116]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ArkadyL
Тогда надо отказаться от всех типов полей, кроме текстового. Не хочется сразу лишаться всех прелестей обработки одной командой.
И почему же станет нельзя обрабатывать одной командой?
14 фев 17, 11:38    [20210158]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Потому что тогда я не смогу сразу в OPENXML указать тип поля. Так как ошибка возможна в любом поле, мне придётся сначала присвоить всем тип varchar, а потом в получившейся таблице проверять значения.
Подозреваю, что автоматически определить ошибочный тег не получится.
14 фев 17, 12:10    [20210267]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ArkadyL
Потому что тогда я не смогу сразу в OPENXML указать тип поля.
А что, в select из того openxml преобразовать столбец в нужный тип уже не подходит?
declare @h int, @x xml = '<doc><data><a>1,1</a><b>2</b></data><data><a>2.1</a><b></b></data><data><a>AAA</a><b>2</b></data></doc>';

exec sp_xml_preparedocument @h output, @x;

select
 try_convert(numeric(18,2), replace(a, ',', '.')), b, c
from
 openxml(@h, '/doc/data', 2)
with
(
 a varchar(100) 'a',
 b int 'b[text() != ""]',
 c int 'c'
);

exec sp_xml_removedocument @h;
14 фев 17, 12:29    [20210333]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
У меня возвращает ошибку 'numeric не является известным имя встроенной функции'
14 фев 17, 13:03    [20210471]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
invm
Member

Откуда: Москва
Сообщений: 9646
ArkadyL
У меня возвращает ошибку 'numeric не является известным имя встроенной функции'
Значит перепишите через cast - будете ловить ошибку на кривых данных, неконвертируемых в numeric. Либо так:
declare @h int, @x xml = '<doc><data><a>1,1</a><b>2</b></data><data><a>2.1</a><b></b></data><data><a>AAA</a><b>2</b></data></doc>';

exec sp_xml_preparedocument @h output, @x;

with a as
(
 select
  a, b, c
 from
  openxml(@h, '/doc/data', 2)
 with
 (
  a varchar(100) 'a',
  b int 'b[text() != ""]',
  c int 'c'
 )
)
select
 cast(nullif(cast('' as xml).query('xs:decimal(sql:column("b.a"))').value('.', 'varchar(30)'), '') as numeric(18,2)),
 a.b,
 a.c
from
 a cross apply
 (select replace(a.a, ',', '.')) b(a);

exec sp_xml_removedocument @h;
14 фев 17, 13:15    [20210517]     Ответить | Цитировать Сообщить модератору
 Re: Как при импорте из XML получать значение пустых тэгов как NULL  [new]
ArkadyL
Member

Откуда: СПб
Сообщений: 156
Просто CAST для значения ААА тоже выдаст ошибку.

Но всё равно спасибо, для своих я воспользуюсь идеей небольшой корректировки полей, а посторонним товарищам верну, пожалуй, файл.
14 фев 17, 14:09    [20210735]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить