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

Откуда:
Сообщений: 7
Имеется web-сервис который пишет логи. Ночью job SQL-сервера загружает их базу,
при этом он выделяет отдельные элементы (в примере это элемент Session) и пишит их в отдельные столбцы.
После подключения сторонних клиентов запрос к сервису стал немножко “отличаться” и как следствие перестал работать запрос.
Задача такая получить значение параметра Session не зависимо от метода (в примере это GetPricelist) и ‘внешних’ клиетов.
Тестовые данные:
declare @t table( x xml);
insert into @t values (
'<?xml version="1.0" encoding="UTF-8"?>
<!--external client-->
<LogSOAPAction>
	<Header DateTime="2011-11-22T00:16:55.549" MethodName="GetPricelist" ExecTime="5781"/>
	<Request>
		<soapenv:Envelope 
		  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
		  xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
		  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
			<soapenv:Header/>
			<soapenv:Body>
				<GetPricelist xmlns="webservice-rik-ru:types-WebServiceIntf">
					<Session>{7C556559-E117-43A5-86A0-F11CDC2DEED7}</Session>
					<APricelistName>Park</APricelistName>
					<ABeginDate>2012-08-27</ABeginDate>
					<AEndDate>2012-09-10</AEndDate>
				</GetPricelist>
			</soapenv:Body>
		</soapenv:Envelope>
	</Request>
</LogSOAPAction>
');

insert into @t values (
'<?xml version="1.0" encoding="UTF-8"?>
<!--internal client-->
<LogSOAPAction>
	<Header DateTime="2011-11-21T15:56:03.160" MethodName="GetPricelist" ExecTime="187"/>
	<Request>
		<SOAP-ENV:Envelope 
		  xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" 
		  xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
		  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
		  xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/">
			<SOAP-ENV:Body SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
				<NS1:GetPricelist xmlns:NS1="urn:webservice-rik-ru:types-WebServiceIntf">
					<Session xsi:type="xsd:string">{4B0001EF-0BF4-47F1-A788-426CC69DDE04}</Session>
					<APricelistName xsi:type="xsd:string">RUS</APricelistName>
					<ABeginDate xsi:type="xsd:dateTime">2011-11-21T00:00:00.000</ABeginDate>
					<AEndDate xsi:type="xsd:dateTime">2011-12-01T00:00:00.000</AEndDate>
				</NS1:GetPricelist>
			</SOAP-ENV:Body>
		</SOAP-ENV:Envelope>
	</Request>
</LogSOAPAction>');

 with XMLNAMESPACES 
  ('http://schemas.xmlsoap.org/soap/envelope/' as [SOAP-ENV])
  select t.c.value('(Header/@DateTime)[1]', 'datetime') as CreationDateTime,
         t.c.value('(Header/@MethodName)[1]', 'nvarchar(250)') as MethodName,
         t.c.value('(Header/@ExecTime)[1]', 'int') as ExecTime,
         t.c.value('(Request/*/*/*/*/.)[1]', 'nvarchar(256)') as Session_New,
         t.c.value('(Request/SOAP-ENV:Envelope/SOAP-ENV:Body/*/Session/.)[1]', 'nvarchar(38)') as Session_Old
    from @t l
         cross apply l.x.nodes('LogSOAPAction') t(c)
Результат:
CreationDateTimeMethodNameExecTimeSession_NewSession_Old
2011-11-22 00:16:55.550GetPricelist5781{7C556559-E117-43A5-86A0-F11CDC2DEED7}NULL
2011-11-21 15:56:03.160GetPricelist187{4B0001EF-0BF4-47F1-A788-426CC69DDE04}{4B0001EF-0BF4-47F1-A788-426CC69DDE04}


Как видно из результата значение Session_Old для первой записи вернет null – этот вариант получения Session использовался ранее, сечас используется вариатн Session_New который в принципе решает поставленную задачу, но тупо спускается по ерархии на нужный уровень. Можно ли написать запрос так чтоб он всё-таки использовал название элемента Session и возвращал значение этого элемента?

Сервер: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Спасибо.
25 ноя 11, 19:02    [11661720]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значений в столбце типа XML  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
?
t.c.value('(Request/*:Envelope/*:Body/*/*:Session/.)[1]', 'nvarchar(38)')
25 ноя 11, 20:32    [11662043]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значений в столбце типа XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
t.c.value('(//Session)[1]', 'nvarchar(38)')
25 ноя 11, 21:11    [11662134]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значений в столбце типа XML  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
andrey odegov
t.c.value('(//Session)[1]', 'nvarchar(38)')
t.c.value('(//*:Session)[1]', 'nvarchar(256)') as Session_New,
t.c.value('(//*:Session)[1]', 'nvarchar(38)') as Session_Old
26 ноя 11, 04:02    [11663324]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значений в столбце типа XML  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
144m, эти два XML почти идентичны. XML-лю глубоко фиолетово на названия префиксов. Названия префиксов нужны только для строкового представления.
Есть особенности синтаксиса запросов для Default namespace. Но Prefix:Name работает независимо от наличия префикса.
WITH XMLNAMESPACES (
	 'http://schemas.xmlsoap.org/soap/envelope/'	AS X
	,'webservice-rik-ru:types-WebServiceIntf'	AS Y
--	,'urn:webservice-rik-ru:types-WebServiceIntf'	AS Y
)SELECT	 R.X.value('Header[1]/@DateTime'			 ,'datetime')		AS CreationDateTime
	,R.X.value('Header[1]/@MethodName'			 ,'NVarchar(250)')	AS MethodName
	,R.X.value('Header[1]/@ExecTime'			 ,'Int')		AS ExecTime
	,R.X.value('(Request/X:Envelope/X:Body/Y:*/Y:Session)[1]','VarChar(38)')	AS Session	-- UniqueIdentifier
	,M.X.value('local-name(.)'				 ,'NVarchar(250)')	AS MethodName
	,M.X.value('./Y:Session[1]'				 ,'VarChar(38)')	AS Session	-- UniqueIdentifier
FROM	@T T
	CROSS APPLY T.X.nodes('/LogSOAPAction[1]')			R(X)
	CROSS APPLY R.X.nodes('Request[1]/X:Envelope[1]/X:Body/Y:*[1]')	M(X)
Если у вас нет единого источника данных (webservice-rik-ru:types-WebServiceIntf), то примените метод daw-а '*:' (т.е. вместо Y подставьте *)

А еще, уберите шапку <?xml version="1.0"?> к XML она явно не относится.

144m
После подключения сторонних клиентов запрос к сервису стал немножко “отличаться”
Не понял связи "внешних клиентов" и изменения формата SOAP.

Писать каждый SOAP в лог?! Может просто валидировать и только ошибки писать?!
27 ноя 11, 16:46    [11667046]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить