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

Откуда:
Сообщений: 136
помогите понять почему не работает?
либо альтернативный способ решения
заранее Спасибо

declare @messagebody xml 
set @messagebody = 
<ns0:getPaymentListResponse xmlns:ns1="urn:uss-wsapi:Types" xmlns:ns0="urn:uss-wsapi:Subscriber"> 
 <PaymentList>
    <ctn>90375</ctn>
    <paymentDate>2016-10-02T00:00:00+03:00</paymentDate>
    <paymentStatus>Funds transfer to</paymentStatus>
    <paymentType>платеж</paymentType>
    <paymentOriginalAmt>195.0</paymentOriginalAmt>
    <paymentCurrentAmt>195.0</paymentCurrentAmt>
    <bankPaymentID>8026</bankPaymentID>
    <paymentActivateDate>2016-10-02T00:00:00+03:00</paymentActivateDate>
  </PaymentList>
  <PaymentList>
    <ctn>96450</ctn>
    <paymentDate>2016-10-01T00:00:00+03:00</paymentDate>
    <paymentStatus>Funds transfer to</paymentStatus>
    <paymentType>платеж</paymentType>
    <paymentOriginalAmt>500.0</paymentOriginalAmt>
    <paymentCurrentAmt>500.0</paymentCurrentAmt>
    <bankPaymentID>8023</bankPaymentID>
    <paymentActivateDate>2016-10-01T00:00:00+03:00</paymentActivateDate>
  </PaymentList>
</ns0:getPaymentListResponse>

select 
	a.value(N'(./ctn)[1]', N'varchar(50)') as ctn,
	(convert(varchar(11),a.value(N'(./paymentDate)[1]', N'date'),1)) as paymentDate, 
	a.value(N'(./paymentStatus)[1]', N'varchar(50)') as paymentStatus,
	a.value(N'(./paymentType)[1]', N'varchar(50)') as paymentType,
	a.value(N'(./paymentOriginalAmt)[1]', N'varchar(50)') as paymentOriginalAmt,
	a.value(N'(./paymentCurrentAmt)[1]', N'varchar(50)') as paymentCurrentAmt,
	a.value(N'(./bankPaymentID)[1]', N'varchar(50)') as bankPaymentID,
	(convert(varchar(11),a.value(N'(./paymentActivateDate)[1]', N'date'),1)) as paymentActivateDate  
	from @messagebody.nodes('declare namespace S="http://schemas.xmlsoap.org/soap/envelope/";declare namespace ns0="urn:uss-wsapi:Subscriber";/ns0:getPaymentListResponse') as r(a);
16 окт 16, 22:42    [19787993]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
M2k
Member

Откуда:
Сообщений: 136
проблема решена

select 
	a.value(N'(./ctn)[1]', N'varchar(50)') as ctn,
	(convert(varchar(11),a.value(N'(./paymentDate)[1]', N'date'),1)) as paymentDate, 
	a.value(N'(./paymentStatus)[1]', N'varchar(50)') as paymentStatus,
	a.value(N'(./paymentType)[1]', N'varchar(50)') as paymentType,
	a.value(N'(./paymentOriginalAmt)[1]', N'varchar(50)') as paymentOriginalAmt,
	a.value(N'(./paymentCurrentAmt)[1]', N'varchar(50)') as paymentCurrentAmt,
	a.value(N'(./bankPaymentID)[1]', N'varchar(50)') as bankPaymentID,
	(convert(varchar(11),a.value(N'(./paymentActivateDate)[1]', N'date'),1)) as paymentActivateDate  
from @xml.nodes('declare namespace S="http://schemas.xmlsoap.org/soap/envelope/"; declare namespace ns0="urn:uss-wsapi:Subscriber"; /ns0:getPaymentListResponse/PaymentList') as r(a);


остался вопрос
Как переделать этот запрос через OPENXML ?
17 окт 16, 02:08    [19788223]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 6201
https://msdn.microsoft.com/en-us/library/ms187367.aspx - третий аргумент. Пример в конце страницы по ссылке.
17 окт 16, 06:19    [19788280]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
aleks2
Guest
Переделай, лучше, на sqlXmlBulkLoad. Фсяко будет быстрее.

Но если те ужо очень хочется, то как-то так

+
-- 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'
....
  
17 окт 16, 06:23    [19788282]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
M2k
Member

Откуда:
Сообщений: 136
Сон Веры Павловны, Доброго вам дня

я только начинаю разбираться в этом, подскажи я правильно понимаю

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ROOT xmlns:xyz="urn:MyNamespace"/>';
в моем случае это
http://schemas.xmlsoap.org/soap/envelope/
или
urn:uss-wsapi:Subscriber";/ns0:getPaymentListResponse'

если не правильно - то подскажи как надо.

Спасибо
17 окт 16, 15:23    [19790796]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
M2k
Member

Откуда:
Сообщений: 136
aleks2,
спасибо, очень полезный совет

повторюсь, я только начинаю разбираться с xml,
какие параметры надо указать в моем случае
тут
exec @ret = sp_xml_preparedocument @hdoc OUTPUT, @doc, '<selena xmlns:ns="http://project.selena-online.ru"/>';
и тут
FROM OPENXML (@hdoc, N'/ns:selena/ns:apartmentprices/ns:apartment/ns:price')
в вашем примере
17 окт 16, 15:32    [19790879]     Ответить | Цитировать Сообщить модератору
 Re: Разбор XML  [new]
aleks2
Guest
declare @doc varchar(max), @err int;

set @doc = '<ns0:selena xmlns:ns0="http://project.selena-online.ru" >
<apartmentprices>
    <apartment id="26">
        <price>6000.00</price>
    </apartment>
    <apartment id="26">
        <price>7000.00</price>
    </apartment>
</apartmentprices>
</ns0:selena>
'
declare @hdoc int, @ret int;

exec @ret = sp_xml_preparedocument @hdoc OUTPUT, @doc, '<ns:selena xmlns:ns="http://project.selena-online.ru"/>';

SELECT  *
FROM OPENXML (@hdoc, N'/ns:selena/apartmentprices/apartment')
WITH (
    apartment_id	int		'@id'
  , price  float		'price[1]'
);

exec @ret = sp_xml_removedocument @hdoc;
17 окт 16, 16:39    [19791377]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить