Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

Откуда:
Сообщений: 30
Здравствуйте.

Успешно формирую xml-файл при помощи кода, подобному коду из FAQ:
DECLARE @result int
DECLARE @OutputFileName varchar(150)
DECLARE @cmd varchar( 150)

Set @OutputFileName = 'c:\mysql.xml'

Set @cmd = 'BCP "EXEC get_xml" queryout "' + @OutputFileName + '" -C ACP -c -r -T'

EXEC @result = master..xp_cmdshell @cmd


Проблема в том, что в результате в файле после последнего закрывающего тэга формируется большое количество ненужных пробелов, что критично не позволяет принять файл на принимающей стороне, на которую я повлиять не могу. Не могу найти способ неручного удаления пробелов после закрывающего тэга.

Подскажите пожалуйста может кто-нудь сталкивался с этой проблемой? или может у кого-то просто мысли есть о ее решении)

Заранее благодарен!
14 авг 17, 21:28    [20723454]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
aleks222
Guest
1. FileTable - лучшее средство.

2. Т.е. ты проверил, что твоя процедура генерирует XML без пробелов в конце?
3. Выводи в файл как binary.
15 авг 17, 05:47    [20723747]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

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

aleks222
1. FileTable - лучшее средство.


Правильно ли я понял что Вы предлагаете формировать xml-файл, который формируется путем выполнения запроса "select ... for xml ..." через процедуру и вызов bcp, в заранее созданную FileTable? если это так, то не могу найти пример реализации) пожалуйста направьте куда надо меня неопытного)

aleks222
2. Т.е. ты проверил, что твоя процедура генерирует XML без пробелов в конце?


Разумеется. Результат формируется в SSMS путем, описанным выше, а там априори не может быть пробелов, так как используется конструкция "for xml"

aleks222
3. Выводи в файл как binary.


аналогично прошу направить на путь истинный. google не может меня понять)

хотел бы дополнить, что очень важно получить универсальный инструмент именно на T-SQL, т.к. процедура формирования файла будет запускаться через различные среды разработки (SSMS, dbForge ...) и через вэб (PHP, JS ...)

Еще раз благодарен)
15 авг 17, 20:30    [20726469]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
iii2
Member

Откуда:
Сообщений: 202
UncleFedor32
aleks222,

aleks222
1. FileTable - лучшее средство.


Правильно ли я понял что Вы предлагаете формировать xml-файл, который формируется путем выполнения запроса "select ... for xml ..." через процедуру и вызов bcp, в заранее созданную FileTable? если это так, то не могу найти пример реализации) пожалуйста направьте куда надо меня неопытного)

Если версия сервера 2012+, можно использовать filetable.
Это таблица особого формата, которая вне sqlserver выглядит как сетевая шара.
Вы можете непосредственно помещать в нее данные с помощью insert, и это будет выглядеть как файл в сетевой шаре.
Это не замена bcp. Но часто проще и эффективнее использовать этот вариант выгрузки.
Ну и документацию искать по тегу Filestreem, filetable.
15 авг 17, 20:51    [20726508]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30751
UncleFedor32
aleks222
3. Выводи в файл как binary.

аналогично прошу направить на путь истинный. google не может меня понять)
опция -n у bcp
15 авг 17, 21:38    [20726595]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
aleks222
Guest
UncleFedor32
Разумеется. Результат формируется в SSMS путем, описанным выше, а там априори не может быть пробелов, так как используется конструкция "for xml"


Наивный чукотский юноша.

UncleFedor32
Правильно ли я понял что Вы предлагаете формировать xml-файл, который формируется путем выполнения запроса "select ... for xml ..." через процедуру и вызов bcp, в заранее созданную FileTable? если это так, то не могу найти пример реализации) пожалуйста направьте куда надо меня неопытного)


Нихера ты не понял. Все проще.
Просто вставь свой xml в таблицу FileTable
, strs as (
  select str = N'"№";"Номер полиса";"Страховая программа";"Колво застрахованных";"Страхователь";"ФИО застрахованных";"Страна";"Дата оформления";"Дата начала действия полиса";"Дата окончания действия полиса";"Название риска";"Страховая сумма";"Валюта";"Страховая премия в валюте";"Страховая премия в Руб";"Путевка";"доп. условия"'
  union all   
  select 
      N' ' + cast([] as nvarchar) + N';'
    + N'"' + [Номер полиса] + N'";'
    + N'"' + [Страховая программа] + N'";'
    + N''  + cast( [Колво застрахованных] as nvarchar) + N';'
    + N'"' + [Страхователь] + N'";'
    + N'"' + [ФИО застрахованных] + N'";'
    + N'"' + [Страна] + N'";'
    + N''  + convert( nvarchar(10), [Дата оформления], 121 ) + N';'
    + N''  + convert( nvarchar(10), [Дата начала действия полиса], 121 ) + N';'
    + N''  + convert( nvarchar(10), [Дата окончания действия полиса], 121 ) + N';'
    + N'"' + [Название риска] + N'";'
    + N''  + replace( cast([Страховая сумма] as nvarchar(64)), '.', ',' ) + N';'
    + N'"' + [Валюта] + N'";'
    + N''  + replace( cast([Страховая премия в валюте] as nvarchar(64)), '.', ',' ) + N';'
    + N''  + replace( cast([Страховая премия в Руб] as nvarchar(64)), '.', ',' ) + N';'
    + N'"' + [Путевка] collate Cyrillic_General_CI_AS + N'";'
    + N'"' + [доп. условия] + N'"'
    from t
)
insert ETL.dbo.AlfaInsurance(name, file_stream) -- таблица FileTable
  values( @filename, cast( cast( (select [str] + @CrLf from strs for xml path('')) as varchar(max) ) as varbinary(max) ) );

if @@ERROR = 0 begin
   set @ReportFilePathName = ETL.dbo.FileTableRootPath('dbo.AlfaInsurance', DEFAULT) + N'\' + @filename;
   set @ReportFileName = @filename;
16 авг 17, 06:03    [20726896]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

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

спасибо большое. сработали) именно то что нужно
16 авг 17, 21:21    [20729570]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

Откуда:
Сообщений: 30
Появилась новая проблема (скорее всего детсадовская) с кодировкой. Требуется формировать xml файл в кодировке UTF-8 в FileTable для последующего его пользования обычным проводником Windows. подскажите пожалуйста "в какую сторону смотреть" для решения проблемы?)
17 авг 17, 20:57    [20732590]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
Ennor Tiegael
Member

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

SQL Server не поддерживает UTF-8, ваш XML сохранится в UTF-16. Хотите переконвертировать - берите внешний конвертер и организуйте постпроцессинг после выгрузки. Или, как вариант, можно написать CLR-сборку, которая будет сохранять ваш файл в нужной кодировке (наверное).
18 авг 17, 03:18    [20732902]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
aleks222
Guest
UncleFedor32
Появилась новая проблема (скорее всего детсадовская) с кодировкой. Требуется формировать xml файл в кодировке UTF-8 в FileTable для последующего его пользования обычным проводником Windows. подскажите пожалуйста "в какую сторону смотреть" для решения проблемы?)

Хе-хе. Проблемы кодировок - такие проблемы...

Нормальные парсеры XML (обычный проводник Windows тоже) поддерживают UTF-16.
Заголовок обеспечьте и не парьтесь.
18 авг 17, 06:02    [20732922]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4378
aleks222
UncleFedor32
Появилась новая проблема (скорее всего детсадовская) с кодировкой. Требуется формировать xml файл в кодировке UTF-8 в FileTable для последующего его пользования обычным проводником Windows. подскажите пожалуйста "в какую сторону смотреть" для решения проблемы?)

Хе-хе. Проблемы кодировок - такие проблемы...

Нормальные парсеры XML (обычный проводник Windows тоже) поддерживают UTF-16.
Заголовок обеспечьте и не парьтесь.


Вот код утилиты на C#, которая может сконвертировать в UTF-16. Кодировки можно задать другие

using System.IO;
using System.Text;

namespace Convert1251Utf8
{
class Program
{
static void Main(string[] args)
{
var bufferSize = 5120000;
var utf8WithoutBom = new System.Text.UTF8Encoding(false);
using (var srcReader = new StreamReader(args[0], Encoding.GetEncoding("windows-1251"), false, bufferSize))
using (var dstWriter = new StreamWriter(args[1], false, utf8WithoutBom, bufferSize))
{
string line;
while ((line = srcReader.ReadLine()) != null)
{
dstWriter.WriteLine(line);
}
}
}
}
}
18 авг 17, 13:36    [20734114]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

Откуда:
Сообщений: 30
aleks222
Хе-хе. Проблемы кодировок - такие проблемы...

Нормальные парсеры XML (обычный проводник Windows тоже) поддерживают UTF-16.


требования принимающей стороны именно UTF-8. повлиять я на это никак не могу)

aleks222
Заголовок обеспечьте и не парьтесь.


Уже пробовал в начале файла генерировать <?xml version="1.0" encoding="UTF-8"?>. Ругается "1 Синтаксический анализ XML: строка 1, символ 38, невозможно переключить кодировку C:\Users\...\SQL1.sql 1 1 "

a_voronin
от код утилиты на C#, которая может сконвертировать в UTF-16. Кодировки можно задать другие


очень важно получить решение именно на T-SQL, т.к. процедура генерирования файла будет запускаться через вэб (PHP или JS) и важно, чтобы уже полноценно готовый файл формировался.
18 авг 17, 19:22    [20735164]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

Откуда:
Сообщений: 30
была мысль запускать cmd из процедуры и там менять кодировку с помощью вызова соответствующего exe-шника. но подходящий exe-шник так и не нашел
18 авг 17, 19:23    [20735170]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
aleks222
Guest
UncleFedor32

Уже пробовал в начале файла генерировать <?xml version="1.0" encoding="UTF-8"?>. Ругается "1 Синтаксический анализ XML: строка 1, символ 38, невозможно переключить кодировку C:\Users\...\SQL1.sql 1 1 "


Больной? Если твой файл имеет кодировку UTF-16 - заголовок нужно писать
N'<?xml version="1.0" encoding="UTF-16"?>'


Перекодировку незатейливо делает powershell.
Но это неправильно.
Неправильному учить не буду.
19 авг 17, 07:51    [20735629]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

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

Почему неправильно? Именно этим способом получилось
20 авг 17, 23:04    [20737537]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
aleks222
Guest
UncleFedor32
aleks222,

Почему неправильно? Именно этим способом получилось


Патаму, что перекодировать правильный XML - идеологически неправильно.
Глупо по любым соображениям.

ЗЫ. Ничо, подрастешь - поймешь.
21 авг 17, 07:26    [20737695]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
UncleFedor32
Member

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

Понял) спасибо, буду расти)
21 авг 17, 09:26    [20737794]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
Владислав Колосов
Member

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

в пакете SSIS можно перекодировать поток UTF-16 в UTF-8, но без BOM. Т.е. потребуется создать файл скриптом с заголовком XML и добавить в него поток из запроса. В общем, ничего сложного и без админского xp_cmdshell.
21 авг 17, 11:05    [20738038]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4378
UncleFedor32

очень важно получить решение именно на T-SQL, т.к. процедура генерирования файла будет запускаться через вэб (PHP или JS) и важно, чтобы уже полноценно готовый файл формировался.



Может оно и важно, но SQL SERVER не умеет. До 2016 версии есть проблемы с конвертацией в UTF. Поэтому вам придется впиливать конвертацию куда-то ещё. Например, перехватить output вашего PHP и сконвертить поток данных там.
22 авг 17, 12:25    [20740953]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4378
UncleFedor32
Здравствуйте.

Успешно формирую xml-файл при помощи кода, подобному коду из FAQ:
DECLARE @result int
DECLARE @OutputFileName varchar(150)
DECLARE @cmd varchar( 150)

Set @OutputFileName = 'c:\mysql.xml'

Set @cmd = 'BCP "EXEC get_xml" queryout "' + @OutputFileName + '" -C ACP -c -r -T'

EXEC @result = master..xp_cmdshell @cmd



Если вы так или иначе дергаете cmdshell, то кто мешает вам вторым шагом дернуть утилиту.
22 авг 17, 12:27    [20740957]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30751
a_voronin
UncleFedor32
очень важно получить решение именно на T-SQL, т.к. процедура генерирования файла будет запускаться через вэб (PHP или JS) и важно, чтобы уже полноценно готовый файл формировался.



Может оно и важно, но SQL SERVER не умеет. До 2016 версии есть проблемы с конвертацией в UTF. Поэтому вам придется впиливать конвертацию куда-то ещё. Например, перехватить output вашего PHP и сконвертить поток данных там.
UncleFedor32 уже получил решение на SQL SERVER. Его последний комментарий относился к варианту Владислав Колосов с SSIS: пакеты неудобно вызывать из T-SQL, особенно многопоточно. Хотя и возможно. Скорость тоже будет не очень, если нужно выгружать множество мелких файлов.
22 авг 17, 14:41    [20741506]     Ответить | Цитировать Сообщить модератору
 Re: экспорт в xml-фаЙл с помощью bcp  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7392
автор
процедура генерирования файла будет запускаться через вэб (PHP или JS)


Вот этот момент я просмотрел. Непонятны бизнес требования, если то, что я процитировал - правда, то там делается все не так.
Для подобного требования используется трехслойная архитектура, а не "примочки".
23 авг 17, 23:16    [20745580]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить