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

Откуда:
Сообщений: 18
Здравствуйте.
Подскажите, пожалуйста.
как корректно импортировать XML, чтобы получилась таблица с полями.

Я нашёл два варианта,
1)

SELECT * into #T FROM
OPENROWSET(Bulk 'C:\Users\a.v.petrov\!!!AllMyDocuments\desc2016.xml', SINGLE_BLOB) [rowsetresults];

2)CREATE TABLE T1(XmlCol xml);

INSERT T1
SELECT CONVERT(xml, BulkColumn, 2) FROM
OPENROWSET(Bulk 'C:\Users\a.v.petrov\!!!AllMyDocuments\desc2016.xml', SINGLE_BLOB) [rowsetresults];

но что делать дальше, чтоб превратить в таблицу не могу понять.
Заранее посмотреть структуру и определить поля не могу. Файл очень большой (через excel не получается развернуть).

буду очень благодарен за помощь.
27 ноя 15, 12:28    [18480915]     Ответить | Цитировать Сообщить модератору
 Re: загрузить из XML файла через BULK insert  [new]
Glory
Member

Откуда:
Сообщений: 104751
avpetrov27
но что делать дальше, чтоб превратить в таблицу не могу понять.

Читать в хелпе все статьи про xml

avpetrov27
Заранее посмотреть структуру и определить поля не могу.

А куда вы собрались смотреть то ? Где эта "структура" у вас имеется/хранится ?
27 ноя 15, 12:33    [18480954]     Ответить | Цитировать Сообщить модератору
 Re: загрузить из XML файла через BULK insert  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31965
avpetrov27
но что делать дальше, чтоб превратить в таблицу не могу понять.
Заранее посмотреть структуру и определить поля не могу. Файл очень большой (через excel не получается развернуть).
Эксель тут при чём???
Нужно начать с того, что бы установить на компьютере софт для работы с текстовыми и XML файлами.
Например, FAR и ещё что то. Может, ещё преобразователи XSLT, например.

И готовьтесь к тому, что раз файл большой, его могут не суметь разобрать стандартные средства, придётся использовать XMLBulkLoad
27 ноя 15, 14:15    [18481590]     Ответить | Цитировать Сообщить модератору
 Re: загрузить из XML файла через BULK insert  [new]
aleks2
Guest
Хе-хе.
https://www.sql.ru/forum/1184999/chto-to-klinit-menya-na-prosteyshem-xml?hl=xml
https://www.sql.ru/forum/1185499/sqlxmlbulkload-s-kornevym-elementom-dlya-kotorogo-ukazano-xmlns?hl=xml

ну и ваще...
https://www.sql.ru/forum/afsearch.aspx?s=xml&bid=1
27 ноя 15, 17:32    [18482793]     Ответить | Цитировать Сообщить модератору
 Re: загрузить из XML файла через BULK insert  [new]
aleks2
Guest
Ну... можно канешно и openxml.
Но это медленнее.
Даром я чтоле тестовое задание делал?
+

-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------
-- 1) Исходя из данных определить таблицу, для хранения цен.

-- вообще то, не совсем понятно: цен чего?
-- но исходя из наличия только одного файла "apartmentprice_26.xml" с ценами - бум считать, что именно его и надо хранить
declare @Prices table(
-- согласно схеме https://selena-online.ru/static/rest/apartmentprice.xsd
id int not null
, apartment_id int not null
, tourid int null
, activatedate datetime null
, expiredate datetime null
, duration_from int not null
, duration_to int not null
, tourpackid int null
-- ну а это собственно цены
, PriceType nvarchar(32) not null
, Price float not null
-- индексы так сразу не придумать, без запросов
-- естественно, в настоящей таблице делать их все уникальными нужды нема.
, primary key nonclustered (id, PriceType)
, unique clustered (apartment_id, tourid, tourpackid, activatedate, duration_from, PriceType, id)
-- , unique (apartment_id, PriceType, id )
-- , unique (tourpackid, PriceType, id )
-- , unique (tourid, PriceType, id )
-- , unique (activatedate, expiredate, PriceType, id)
-- , unique (duration_from, duration_to, PriceType, id)
);
-------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------

-- 2)Написать или описать ПО, которое сможет загружать эти данные в таблицу из пункта 1

-- сервер сам способен загружать такие данные
declare @doc varchar(max), @err int;

-- 1. грузим файл
select @doc = BulkColumn from OPENROWSET(BULK N'E:\SQL Data\apartmentprice_26.xml', SINGLE_BLOB) AS Doc;
set @err = @@error;
if @err <> 0 begin
raiserror('Что-то пошло не так в OPENROWSET @@error = %i', 16, 1, @err);
return;
end;

-- 2. Поскольку предполагается, что файл большой - запускаем sp_xml_preparedocument
declare @hdoc int, @ret int;

exec @ret = sp_xml_preparedocument @hdoc OUTPUT, @doc, '<selena xmlns:ns="http://project.selena-online.ru"/>';
if @ret <> 0 begin
raiserror('Что-то пошло не так в sp_xml_preparedocument @ret = %i', 16, 1, @ret);
return;
end;

-- 3. разбираем XML в плоскую таблицу
insert @Prices
select
id
, apartment_id
, tourid
, activatedate
, expiredate
-- вообще то эти параметры объявлены в схеме обязательными
, isnull( duration_from, 1 )
, isnull( duration_to, power( cast(2 as bigint),31) - 1 )
---------
, tourpackid
, PriceType
, Price
from
(
SELECT *
FROM OPENXML (@hdoc, N'/ns:selena/ns:apartmentprices/ns:apartment/ns:price')
WITH (
apartment_id int '../@id'

, id int '@id'
, tourid int '@tourid'
, activatedate datetime'@activatedate'
, expiredate datetime'@expiredate'
, duration_from int '@duration_from'
, duration_to int '@duration_to'
, tourpackid int '@tourpackid'

--конечно, можно собрать это динамически, но это не задавали
, price_m1 float '@price_m1'
, price_m2 float '@price_m2'
, price_m3 float '@price_m3'
, price_m4 float '@price_m4'
, price_m5 float '@price_m5'

, price_m1_2 float '@price_m1_2'
, price_m1_3 float '@price_m1_3'
, price_m1_4 float '@price_m1_4'
, price_m1_5 float '@price_m1_5'
, price_m1_6 float '@price_m1_6'
, price_m1_7 float '@price_m1_7'

, price_m2_2 float '@price_m2_2'
, price_m2_3 float '@price_m2_3'
, price_m2_4 float '@price_m2_4'
, price_m2_5 float '@price_m2_5'
, price_m2_6 float '@price_m2_6'
, price_m2_7 float '@price_m2_7'

, price_m3_2 float '@price_m3_2'
, price_m3_3 float '@price_m3_3'
, price_m3_4 float '@price_m3_4'
, price_m3_5 float '@price_m3_5'
, price_m3_6 float '@price_m3_6'
, price_m3_7 float '@price_m3_7'

, price_m4_2 float '@price_m4_2'
, price_m4_3 float '@price_m4_3'
, price_m4_4 float '@price_m4_4'
, price_m4_5 float '@price_m4_5'
, price_m4_6 float '@price_m4_6'
, price_m4_7 float '@price_m4_7'

, price_m5_2 float '@price_m5_2'
, price_m5_3 float '@price_m5_3'
, price_m5_4 float '@price_m5_4'
, price_m5_5 float '@price_m5_5'
, price_m5_6 float '@price_m5_6'
, price_m5_7 float '@price_m5_7'

, price_1 float '@price_1'
, price_2 float '@price_2'
, price_3 float '@price_3'
, price_4 float '@price_4'
, price_5 float '@price_5'

, price_1_2 float '@price_1_2'
, price_1_3 float '@price_1_3'
, price_1_4 float '@price_1_4'
, price_1_5 float '@price_1_5'
, price_1_6 float '@price_1_6'
, price_1_7 float '@price_1_7'

, price_2_2 float '@price_2_2'
, price_2_3 float '@price_2_3'
, price_2_4 float '@price_2_4'
, price_2_5 float '@price_2_5'
, price_2_6 float '@price_2_6'
, price_2_7 float '@price_2_7'

, price_3_2 float '@price_3_2'
, price_3_3 float '@price_3_3'
, price_3_4 float '@price_3_4'
, price_3_5 float '@price_3_5'
, price_3_6 float '@price_3_6'
, price_3_7 float '@price_3_7'

, price_4_2 float '@price_4_2'
, price_4_3 float '@price_4_3'
, price_4_4 float '@price_4_4'
, price_4_5 float '@price_4_5'
, price_4_6 float '@price_4_6'
, price_4_7 float '@price_4_7'

, price_5_2 float '@price_5_2'
, price_5_3 float '@price_5_3'
, price_5_4 float '@price_5_4'
, price_5_5 float '@price_5_5'
, price_5_6 float '@price_5_6'
, price_5_7 float '@price_5_7'

, price_c1 float '@price_c1'
, price_c2 float '@price_c2'
, price_c3 float '@price_c3'
, price_c4 float '@price_c4'
, price_c5 float '@price_c5'

, price_c1_2 float '@price_c1_2'
, price_c1_3 float '@price_c1_3'
, price_c1_4 float '@price_c1_4'
, price_c1_5 float '@price_c1_5'
, price_c1_6 float '@price_c1_6'
, price_c1_7 float '@price_c1_7'

, price_c2_2 float '@price_c2_2'
, price_c2_3 float '@price_c2_3'
, price_c2_4 float '@price_c2_4'
, price_c2_5 float '@price_c2_5'
, price_c2_6 float '@price_c2_6'
, price_c2_7 float '@price_c2_7'

, price_c3_2 float '@price_c3_2'
, price_c3_3 float '@price_c3_3'
, price_c3_4 float '@price_c3_4'
, price_c3_5 float '@price_c3_5'
, price_c3_6 float '@price_c3_6'
, price_c3_7 float '@price_c3_7'

, price_c4_2 float '@price_c4_2'
, price_c4_3 float '@price_c4_3'
, price_c4_4 float '@price_c4_4'
, price_c4_5 float '@price_c4_5'
, price_c4_6 float '@price_c4_6'
, price_c4_7 float '@price_c4_7'

, price_c5_2 float '@price_c5_2'
, price_c5_3 float '@price_c5_3'
, price_c5_4 float '@price_c5_4'
, price_c5_5 float '@price_c5_5'
, price_c5_6 float '@price_c5_6'
, price_c5_7 float '@price_c5_7'

, price_lock float '@price_lock'
, price_lock_2 float '@price_lock_2'
, price_lock_3 float '@price_lock_3'
, price_lock_4 float '@price_lock_4'
, price_lock_5 float '@price_lock_5'
, price_lock_6 float '@price_lock_6'
, price_lock_7 float '@price_lock_7'
)
) as X
UNPIVOT
(Price FOR PriceType IN
(
price_m1
, price_m2
, price_m3
, price_m4
, price_m5

, price_m1_2
, price_m1_3
, price_m1_4
, price_m1_5
, price_m1_6
, price_m1_7

, price_m2_2
, price_m2_3
, price_m2_4
, price_m2_5
, price_m2_6
, price_m2_7

, price_m3_2
, price_m3_3
, price_m3_4
, price_m3_5
, price_m3_6
, price_m3_7

, price_m4_2
, price_m4_3
, price_m4_4
, price_m4_5
, price_m4_6
, price_m4_7

, price_m5_2
, price_m5_3
, price_m5_4
, price_m5_5
, price_m5_6
, price_m5_7

, price_1
, price_2
, price_3
, price_4
, price_5

, price_1_2
, price_1_3
, price_1_4
, price_1_5
, price_1_6
, price_1_7

, price_2_2
, price_2_3
, price_2_4
, price_2_5
, price_2_6
, price_2_7

, price_3_2
, price_3_3
, price_3_4
, price_3_5
, price_3_6
, price_3_7

, price_4_2
, price_4_3
, price_4_4
, price_4_5
, price_4_6
, price_4_7

, price_5_2
, price_5_3
, price_5_4
, price_5_5
, price_5_6
, price_5_7

, price_c1
, price_c2
, price_c3
, price_c4
, price_c5

, price_c1_2
, price_c1_3
, price_c1_4
, price_c1_5
, price_c1_6
, price_c1_7

, price_c2_2
, price_c2_3
, price_c2_4
, price_c2_5
, price_c2_6
, price_c2_7

, price_c3_2
, price_c3_3
, price_c3_4
, price_c3_5
, price_c3_6
, price_c3_7

, price_c4_2
, price_c4_3
, price_c4_4
, price_c4_5
, price_c4_6
, price_c4_7

, price_c5_2
, price_c5_3
, price_c5_4
, price_c5_5
, price_c5_6
, price_c5_7

, price_lock
, price_lock_2
, price_lock_3
, price_lock_4
, price_lock_5
, price_lock_6
, price_lock_7
)
) as unp;

exec @ret = sp_xml_removedocument @hdoc;

--select * from @prices order by id, PriceType;

---------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
-- 3) Предоставить запрос, которые по параметрам туристов возвращал бы лучшую цену для заданного размещения.
-- Пример параметров запроса 2 взрослых + ребенок 7 лет.

-- кто бы еще разъяснил что такое "заданное размещение"?
-- ну, предположим, что это apartment_id.
-- Делать то нечего.
-- 1. Вобще то, эти возрастные категории могут быть разными. Для разных объектов.
-- 2. Точно также, туры имеют разную длительность, что еще сильнее запутывает понятие "лучшая цена".
-- 3. Таким образом, остается только вариант - "лучше" = "тур, который дешевле за один день".
-- 4. Но дней то в неделе семь и все могут иметь разную цену.
-- вобщем, постановка подкачала.

declare @pt_adult nvarchar(100), @pt_child nvarchar(100), @apartment_id int, @adult int, @child int;

-- параметры запроса
select
@apartment_id = 26 -- ну... больше там ничего нет
, @adult = 2 -- число взрослых
, @child = 1 -- число детей
, @pt_adult = 'price_m5' -- взрослые
, @pt_child = 'price_m3'; -- дите 7 годиков, можно заменить на 'price_с3', но как-то стремно дитю 7 лет без места...

with
apartments as (select * from @Prices where apartment_id = @apartment_id)
, child as (select * from apartments where PriceType like @pt_child+'%' )
, adult as (select * from apartments where PriceType like @pt_adult+'%' )
, [a&c] as ( select a.id, a.tourid, a.Price as APrice, c.Price as CPrice
from adult a
inner join child c on a.id = c.id
and (
( substring(a.PriceType, len(@pt_adult)+1, 100) = substring(c.PriceType, len(@pt_child)+1, 100) )
or (
( a.PriceType = @pt_adult)
and not exists(select * from adult where id = a.id and PriceType = c.PriceType )
)
or (
( c.PriceType = @pt_child)
and not exists(select * from child where id = a.id and PriceType = a.PriceType )
)
)
)
-- top(3) можно заменить на top(1), если нужно абсолютно точное соответствие заданию
select top(3) with ties tourid, @adult*APrice + @child*CPrice as totalPrice from [a&c] order by 2 asc;

-- ну вот и все.
-- Аминь.


Данные apartmentprice_26.xml'
+
<?xml version="1.0" encoding="utf-8"?>
<selena xmlns="http://project.selena-online.ru" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://project.selena-online.ru https://selena-online.ru/static/rest/apartmentprice.xsd">
<apartmentprices>
<apartment id="26">
<price id="922" tourid="292" price_c3="6000.00"
price_c2="4500.00"
price_m5="16800.00"
price_m3="16800.00"
price_m4="16800.00"
/>
</apartment>
<apartment id="26">
<price id="923" tourid="293" price_c3="11000.00"
price_c2="8300.00"
price_m5="39300.00"
price_m3="39300.00"
price_m4="39300.00"
/>
</apartment>
<apartment id="26">
<price id="924" tourid="295" price_c3="6000.00"
price_c2="4500.00"
price_m5="22300.00"
price_m3="22300.00"
price_m4="22300.00"
/>
</apartment>
<apartment id="26">
<price id="925" tourid="296" price_c3="3000.00"
price_c2="2300.00"
price_m5="12400.00"
price_m3="12400.00"
price_m4="12400.00"
/>
</apartment>
<apartment id="26">
<price id="926" tourid="298" price_c3="10000.00"
price_c2="7500.00"
price_m5="45300.00"
price_m3="45300.00"
price_m4="45300.00"
/>
</apartment>
<apartment id="26">
<price id="927" tourid="297" price_c3="18000.00"
price_c2="13500.00"
price_m5="74200.00"
price_m3="74200.00"
price_m4="74200.00"
/>
</apartment>
<apartment id="26">
<price id="928" tourid="299" price_c3="9000.00"
price_c2="6750.00"
price_m5="37100.00"
price_m3="37100.00"
price_m4="37100.00"
/>
</apartment>
<apartment id="26">
<price id="929" tourid="300" price_c3="8000.00"
price_c2="6000.00"
price_m5="30600.00"
price_m3="30600.00"
price_m4="30600.00"
/>
</apartment>
<apartment id="26">
<price id="930" tourid="301" price_c3="3000.00"
price_c2="2300.00"
price_m5="12400.00"
price_m3="12400.00"
price_m4="12400.00"
/>
</apartment>
<apartment id="26">
<price id="931" tourid="302" price_c3="8000.00"
price_c2="6000.00"
price_m5="30600.00"
price_m3="30600.00"
price_m4="30600.00"
/>
</apartment>
<apartment id="26">
<price id="932" tourid="303" price_c3="6000.00"
price_c2="4500.00"
price_m5="22300.00"
price_m3="22300.00"
price_m4="22300.00"
/>
</apartment>
<apartment id="26">
<price id="933" tourid="304" price_c3="14000.00"
price_c2="10500.00"
price_m5="58000.00"
price_m3="58000.00"
price_m4="58000.00"
/>
</apartment>
<apartment id="26">
<price id="934" tourid="305" price_c3="3000.00"
price_c2="2300.00"
price_m5="12400.00"
price_m3="12400.00"
price_m4="12400.00"
/>
</apartment>
<apartment id="26">
<price id="935" tourid="306" price_c3="8000.00"
price_c2="6000.00"
price_m5="30600.00"
price_m3="30600.00"
price_m4="30600.00"
/>
</apartment>
<apartment id="26">
<price id="936" tourid="307" price_c3="7000.00"
price_c2="5250.00"
price_m5="22300.00"
price_m3="22300.00"
price_m4="22300.00"
/>
</apartment>
<apartment id="26">
<price id="937" tourid="308" price_c3="14000.00"
price_c2="10500.00"
price_m5="44800.00"
price_m3="44800.00"
price_m4="44800.00"
/>
</apartment>
<apartment id="26">
<price id="938" tourid="309" price_c3="6000.00"
price_c2="4500.00"
price_m5="16800.00"
price_m3="16800.00"
price_m4="16800.00"
/>
</apartment>
<apartment id="26">
<price id="939" tourid="310" price_c3="12000.00"
price_c2="9000.00"
price_m5="31700.00"
price_m3="31700.00"
price_m4="31700.00"
/>
</apartment>
<apartment id="26">
<price id="920" tourid="311" price_c3="3000.00"
price_c2="2300.00"
price_m5="7400.00"
price_m3="7400.00"
price_m4="7400.00"
/>
</apartment>
</apartmentprices>

</selena>

27 ноя 15, 17:38    [18482834]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить