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

Откуда:
Сообщений: 11
Здравствуйте!
Я меня есть файл типа XML, в котором несколько вложенных таблиц.
Подскажите, каким образом можно включить данные в таблицы SQL?
(Один XML в одну таблицу SQL- это мне известно)
28 мар 17, 15:57    [20342221]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
nachin,
в xml нет вложенных таблиц - только вложенный xml.

Определитесь с задачей, а затем в соответствии с правилами предоставьте:
1. Исходный материал
2. Описание и/или конечный результат.
3. Описание/код, который вы уже спели сделать, но не смогли из п.1 сделать п.2.
В случае отсутствия п.3, вам нужно разместить пост в разделе "Работа" с указанием сроков исполнения и величины вознаграждения.
28 мар 17, 16:19    [20342382]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
nachin
Member

Откуда:
Сообщений: 11
Данные файла XML нужно записать в таблицы DOP_PAC, OMS и LGOT

<?xml version = "1.0" encoding="Windows-1251" standalone="yes"?>
<ZL_LIST>
<ZGLV>
<VERSION>1.0</VERSION>
<DATA>2017-03-23</DATA>
<FILENAME>OTLADKA</FILENAME>
<SENDER>948E147A</SENDER>
</ZGLV>
<PACK>
<CODE>1</CODE>
<DAT_B>2017-03-23</DAT_B>
<DAT_E>2017-03-23</DAT_E>
</PACK>
<ZAP>
<N_ZAP>1</N_ZAP>
<DOP_PAC>
<ID_PAC>1</ID_PAC>
<OMS>
<ID_PAC>1</ID_PAC>
<VPLS>1</VPLS>
<SPLS>1234567890</SPLS>
<NPLS>12345678901234567890</NPLS>
<SMOCOD>12345</SMOCOD>
<SOC>112</SOC>
</OMS>
<LGOT>
<ID_PAC>1</ID_PAC>
<NDOK>1234567890123456</NDOK>
</LGOT>
</DOP_PAC>
</ZAP>
</ZL_LIST>
28 мар 17, 16:42    [20342529]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
nachin,
google
28 мар 17, 16:47    [20342580]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Руслан Дамирович, что я делаю не так?
declare @x as xml

with a as (
select 1 a, '1' b
union all
select 2, '2'
union all
select 3, '3'
union all
select 4, '4'
union all
select 5, '5'
union all
select 6, '6')

select @x = (select * from a for xml path ('row'))
select @x

select Tbl.Col.value ('@a','int'),
Tbl.Col.value('@b','varchar')
 from @x.nodes('row') Tbl(Col)


<row><a>1</a><b>1</b></row>
<row><a>2</a><b>2</b></row>
<row><a>3</a><b>3</b></row>
<row><a>4</a><b>4</b></row>
<row><a>5</a><b>5</b></row>
<row><a>6</a><b>6</b></row>


NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
28 мар 17, 18:53    [20343193]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Шыфл,
а так работает

select Tbl.Col.value ('a[1]','int'),
Tbl.Col.value('b[1]','varchar(10)')
 from @x.nodes('row') Tbl(Col)
28 мар 17, 18:55    [20343208]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
Хех, работает!
Только первую строчку
<?xml version = "1.0" encoding="Windows-1251" standalone="yes"?>
нужно удалить

+

declare @x as xml

set @x='

<ZL_LIST>
<ZGLV>
<VERSION>1.0</VERSION>
<DATA>2017-03-23</DATA>
<FILENAME>OTLADKA</FILENAME>
<SENDER>948E147A</SENDER>
</ZGLV>
<PACK>
<CODE>1</CODE>
<DAT_B>2017-03-23</DAT_B>
<DAT_E>2017-03-23</DAT_E>
</PACK>
<ZAP>
<N_ZAP>1</N_ZAP>
<DOP_PAC>
<ID_PAC>1</ID_PAC>
<OMS>
<ID_PAC>1</ID_PAC>
<VPLS>1</VPLS>
<SPLS>1234567890</SPLS>
<NPLS>12345678901234567890</NPLS>
<SMOCOD>12345</SMOCOD>
<SOC>112</SOC>
</OMS>
<LGOT>
<ID_PAC>1</ID_PAC>
<NDOK>1234567890123456</NDOK>
</LGOT>
</DOP_PAC>
</ZAP>
</ZL_LIST> '

select @x

select Tbl.Col.value ('ID_PAC[1]','int')
from @x.nodes('ZL_LIST/ZAP/DOP_PAC') Tbl(Col)

select
Tbl.Col.value ('ID_PAC[1]','int'),
Tbl.Col.value ('VPLS[1]','int'),
Tbl.Col.value ('SPLS[1]','varchar(30)'),
Tbl.Col.value ('NPLS[1]','varchar(30)'),
Tbl.Col.value ('SMOCOD[1]','varchar(30)'),
Tbl.Col.value ('SOC[1]','varchar(30)')
from @x.nodes('ZL_LIST/ZAP/DOP_PAC/OMS') Tbl(Col)

select
Tbl.Col.value('ID_PAC[1]','int'),
Tbl.Col.value('NDOK[1]','varchar(30)')
from @x.nodes('ZL_LIST/ZAP/DOP_PAC/LGOT') Tbl(Col)

28 мар 17, 19:07    [20343246]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
Вот, у меня тут тоже задача намечается по загрузке файла XML в MS SQL 2008 R2 с раскидыванием по таблицам. Когда-то давно я писал отдельно VBScript для решения этой задачи. А вообще, есть варианты с минимальными трудозатратами исключительно средствами T-SQL?
29 мар 17, 10:46    [20345057]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
aleks2
Guest
Dmitry V. Liseev
Вот, у меня тут тоже задача намечается по загрузке файла XML в MS SQL 2008 R2 с раскидыванием по таблицам. Когда-то давно я писал отдельно VBScript для решения этой задачи. А вообще, есть варианты с минимальными трудозатратами исключительно средствами T-SQL?


Если освоишь SqlXmlBulkLoad - быстрее и минимальнее нема.

Но схемы надо уметь писать.
Рыбу хорошо делает xsd.exe из утилит студии.
29 мар 17, 14:05    [20346421]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
hoolygan
Member

Откуда:
Сообщений: 14
Еще 1 подсказка - temporary tables, каждая на свою ноду - а потом играйтесь как душе угодно
29 мар 17, 15:20    [20346952]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
aleks2
Dmitry V. Liseev
Вот, у меня тут тоже задача намечается по загрузке файла XML в MS SQL 2008 R2 с раскидыванием по таблицам. Когда-то давно я писал отдельно VBScript для решения этой задачи. А вообще, есть варианты с минимальными трудозатратами исключительно средствами T-SQL?


Если освоишь SqlXmlBulkLoad - быстрее и минимальнее нема.
Это я уже освоил через внешний скрипт:
Dim FileValid set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0") 

aleks2
Но схемы надо уметь писать.
Рыбу хорошо делает xsd.exe из утилит студии.
У меня текстовые структурированные данные. Это логи работы приложения. Без наворотов. Хотелось бы просто запустить хранимку и указать файл. Пусть сама создаст все нужные таблицы. Грубо говоря, мне нужны простейшие запросы по xml файлам в миллионы узлов. Я хочу загрузить в таблицы и там писать запросы SQL. Вот это понимается под минимальными трудозатратами. Под каждый формат лога создавать схему и делать скрипт загрузки не хочется. Там отличия только в названиях тегов и прочих мелочах.
29 мар 17, 15:52    [20347188]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
hoolygan
Member

Откуда:
Сообщений: 14
Ну правильно, хранимка принимает на вход xml-строку, а в хранимке ее обрабатываешь, создаешь 3 временные таблички, парсишь xml-ку в эти таблички, а потом работаешь уже с ними.
29 мар 17, 16:31    [20347455]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
aleks2
Guest
Dmitry V. Liseev
понимается под минимальными трудозатратами. Под каждый формат лога создавать схему и делать скрипт загрузки не хочется. Там отличия только в названиях тегов и прочих мелочах.


Тебе прямой путь в управдомы.
29 мар 17, 18:40    [20348196]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8823
Dmitry V. Liseev,

XQUERY? Нет, не слышал. Но очень надо на том, что знаю.
30 мар 17, 12:59    [20350499]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1252
О, смотрю кто-то тоже в медстраховании работает.
Вот только что меня бесит это что фонды областях берут за базу федеральные структуры и наворачивают их как хотят. Т.е с одной стороны некая общая схема есть, а по факту нет :)

Мы тоже шли по пути загрузки из xml файлов сразу в таблицы на сервере, но к сожалению уперлись в то что наш фонд работает с xml файлом как с обычным текстовым файлом, парсер у них такой :) Вобщем мой вам совет пишите загрузчик как это сделали мы, опять же пользователю приятно когда у него прогресс какой-то на экране отрисовывается. По факту мы грузим сначала все данные в таблицы в память приложения попутно делая различные проверки значений параметров и прочие зависимости данных, затем очень быстро закидываем данные в промежуточные таблицы на сервере и затем уже хранимкой перекидываем их в рабочие таблицы которые спроектированы лучше чем у федералов.
30 мар 17, 13:34    [20350695]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
nachin
Member

Откуда:
Сообщений: 11
У меня в XML три уровня вложенности. В одну из таблиц SQL должно войти поле из первого уровня, поле из второго и поле из третьего. Поля со второго и третьего уровня через OPENXML отлично садятся, а вот вместо поля с первого уровня записываются NULL. Подскажите, кто уже сталкивался с такой проблемой
30 мар 17, 15:42    [20351455]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
nachin
Member

Откуда:
Сообщений: 11
У меня в XML три уровня вложенности. В одну из таблиц SQL должно войти поле из первого уровня, поле из второго и поле из третьего. Поля со второго и третьего уровня через OPENXML отлично садятся, а вот вместо поля с первого уровня записываются NULL. Подскажите, кто уже сталкивался с такой проблемой
30 мар 17, 15:51    [20351513]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
nachin
Member

Откуда:
Сообщений: 11
Пример:
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='
<?xml version = "1.0" encoding="Windows-1251" standalone="yes"?>
<ZGLV>
<UROV1>
<NZAP>1</NZAP>
<UROV2>
<IDZAP>1</IDZAP>
<UROV3>
<IDPAC>11</IDPAC>
<IDPACNAME>pl1</IDPACNAME>
</UROV3>
<UROV3>
<IDPAC>12</IDPAC>
<IDPACNAME>pl2</IDPACNAME>
</UROV3>
</UROV2>
<NZAP>2</NZAP>
<UROV2>
<IDZAP>11</IDZAP>
<UROV3>
<IDPAC>21</IDPAC>
<IDPACNAME>p21</IDPACNAME>
</UROV3>
<UROV3>
<IDPAC>22</IDPAC>
<IDPACNAME>p22</IDPACNAME>
</UROV3>
</UROV2>
</UROV1>
</ZGLV> '

EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT *
FROM
OPENXML (@idoc, '/ZGLV/UROV1/UROV2/UROV3', 3)
WITH (
NZAP int '../../NZAP',
IDZAP int '../IDZAP',
IDPAC int 'IDPAC',
IDPACNAME varchar(10) 'IDPACNAME')
30 мар 17, 16:45    [20351824]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 776
nachin,

а что в примере не работает, простите за нескромный вопрос?
30 мар 17, 18:33    [20352161]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
Dmitry V. Liseev
Member [заблокирован]

Откуда: Санкт-Петербург
Сообщений: 5489
энди
О, смотрю кто-то тоже в медстраховании работает.
Вот только что меня бесит это что фонды областях берут за базу федеральные структуры и наворачивают их как хотят. Т.е с одной стороны некая общая схема есть, а по факту нет :)

Мы тоже шли по пути загрузки из xml файлов сразу в таблицы на сервере, но к сожалению уперлись в то что наш фонд работает с xml файлом как с обычным текстовым файлом, парсер у них такой :) Вобщем мой вам совет пишите загрузчик как это сделали мы, опять же пользователю приятно когда у него прогресс какой-то на экране отрисовывается. По факту мы грузим сначала все данные в таблицы в память приложения попутно делая различные проверки значений параметров и прочие зависимости данных, затем очень быстро закидываем данные в промежуточные таблицы на сервере и затем уже хранимкой перекидываем их в рабочие таблицы которые спроектированы лучше чем у федералов.
+1
Я тоже убедился, что это наиболее гибкая и эффективная архитектура.
31 мар 17, 15:07    [20354933]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1252
У нас где-то 2000 тысячи счетов за месяц за 2 часа в базу аккуратно укладываются, это с разархивированием, парсингом xml, закидыванием в промежуточные таблицы на сервере и затем раскидывание данных по рабочим таблицам. А у Вас как с быстродействием?
1 апр 17, 10:03    [20356600]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
энди
Мы тоже шли по пути загрузки из xml файлов сразу в таблицы на сервере
Это в принципе не может работать, хоть с XML, хоть ещё с чем то, так люди обычно делают свой первый проект :-)
Реальность такова, что нужно проверять данные, преобразовывать, если есть неправильные данные, не вставлять их, формируя соотв. отчёт, а остальное вставлять, или вставлять всё в "первичку в том виде, как пришло", ну и так далее. А как в учебнике, замапить мышкой и всё, работает - так не получается.


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

Мы делаем приложением, делаем некую небольшую обработку делать сразу, получаем минус одно преобразование.
Но пока всё быстродействие не выжали, нужно бы заменить обработку XML на потоковое, с XDocument на XMLReader
1 апр 17, 11:43    [20356707]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
энди
Member

Откуда: Киров, Россия
Сообщений: 1252
Ну понятно что мы тоже SqlXmlBulkLoad не сразу в рабочие таблицы загоняли, а в промежуточные, мы же не больные на всю голову :)
У меня вот какой вопрос, а разве SqlXmlBulkLoad не потоком гонит, мы им БД ФИАС загружали, а там реально гигабайнтные xml и проблем небыло, что-то не верится что он там весь документ в память загоняет.
1 апр 17, 13:14    [20356831]     Ответить | Цитировать Сообщить модератору
 Re: Добавление в таблицы SQL из XML  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31970
энди
а разве SqlXmlBulkLoad не потоком гонит, мы им БД ФИАС загружали, а там реально гигабайнтные xml и проблем небыло, что-то не верится что он там весь документ в память загоняет.
Да, конечно, он как раз потоком.

Я про обработку XML на C#, или каких то других языках.
Там есть соблазн сразу считать весь XML в некий объект, что бы потом было просто с ним работать.

Вот xslt, кстати, тоже появилось потоковое, тоже вещь.
1 апр 17, 15:03    [20356926]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить