Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сравнить поля XML  [new]
16012017
Guest
Здравствуйте !
Делаю механизм Журналирования изменения данных.
Создал табличку
CREATE TABLE Journal(Operation int, TableName varchar(100), IdTable int, SysDate datetime, UserName varchar(50),
                     NewValue xml, OldValue xml)

куда триггерами сливаю данные из таблиц, где
select @OldValue = (select * from deleted R for xml auto, type)
select @NewValue = (select * from inserter R for xml auto, type)

то есть в полях OldValue и NewValue храниятся данные до и после изменения таблицы.
Всё прекрасно логируется (таблича растёт как на дрожжах )

Следующий этап, нужно показать пользователю, какое значение (или значения) поменялись. для этого нужно сравнить два XML, причём атрибуты в одном, могут отсутствовать в другом (null).

Подскажите пожалуйста, как это можно реализовать ? Как из двух XML вытащить общую структуру ?
16 янв 17, 15:56    [20110828]     Ответить | Цитировать Сообщить модератору
 Re: Сравнить поля XML  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 4073
?

http://stackoverflow.com/questions/9013680/t-sql-how-can-i-compare-two-variables-of-type-xml-when-length-varcharmax
16 янв 17, 16:03    [20110880]     Ответить | Цитировать Сообщить модератору
 Re: Сравнить поля XML  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 942
16012017,

комментировать спорное решение для журналирования изменений я не буду более, чем уже,
но попробую подтолкнуть к "изобретению" убогого костыля для этого убогого решения
DECLARE @xml XML = (
SELECT TOP 1 * FROM sys.columns FOR XML AUTO, TYPE )

DECLARE @hDoc INT
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml
;
WITH
d AS (
  SELECT 
    *
  FROM 
    OPENXML( @hDoc, '//*' )  
)
SELECT
  d1.localname,
  d2.text
FROM
  d d1
  INNER JOIN d d2 ON (
        d2.[parentid] = d1.[id]
    AND d2.[nodetype] = 3 )
WHERE
  d1.nodetype = 2
;
EXEC sp_xml_removedocument @hDoc
16 янв 17, 16:55    [20111135]     Ответить | Цитировать Сообщить модератору
 Re: Сравнить поля XML  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
имхо решение слабо жизненное, если не хотите сильно ломать то SPARSE + XML FOR ALL_SPARSE_COLUMNS перелить и жить нормально с теми де триггерами, а работать уже более правильно
16 янв 17, 17:01    [20111175]     Ответить | Цитировать Сообщить модератору
 Re: Сравнить поля XML  [new]
16012017
Guest
Решил сделать так: слить в одну табличку значения двух XML через FULL JOIN и объеденить их по атрибутам.
Получилось нечто подобное, где в первой строчке атрибут который отсутствует в OldXML, но есть в NewXML
Картинка с другого сайта.
Далее простым where isnull(OldValue,'') <> isnull(NewValue,'') отсеиваем совпадающие значения.

declare @TmpJ table(Id int identity, OldValue xml, NewValue xml, SysDate datetime, UserName varchar(max))

-- значения для теста
insert into @TmpJ
values('<R IdEmployee="23345" IdMonth="6" TheYear="2016" SDate="2016-06-15T00:00:00" FDate="2016-06-15T00:00:00" NameDay="" IdPost="4029" />',
       '<R IdEmployee="23345" IdMonth="6" TheYear="2016" SDate="2016-06-15T00:00:00" FDate="2016-06-15T00:00:00" BasisPrivilege="27-5" NameDay="" IdPost="4029" />',
       null, 'Иванов'),
       ('<R IdEmployee="23345" IdMonth="6" TheYear="2016" SDate="2016-06-01T00:00:00" FDate="2016-06-11T00:00:00" NameDay="ДЛОТПУСК" IdPost="4029" />',
       '<R IdEmployee="23345" IdMonth="6" TheYear="2016" SDate="2016-06-01T00:00:00" FDate="2016-06-14T00:00:00" BasisPrivilege="27-5" NameDay="ДЛОТПУСК" IdPost="4029" />',
       null, 'Петров')

declare @Id int, @OldXML xml, @NewXml xml, @UserName varchar(max), @SysDate datetime

select * from @TmpJ

while exists(select * from @TmpJ) begin
  select top 1 @Id = Id,
         @OldXML = OldValue,
         @NewXML = NewValue,
         @UserName = UserName,
         @SysDate = SysDate
    from @TmpJ

  --insert into @Res(AName, AText, UserName, SysDate)
  select 'Изменено',
         AText = convert(varchar(max),P.Value) + ': с '''+isnull(OldValue,'')+''' на '''+isnull(NewValue,'')+'''',
         @UserName, @SysDate
    from (select Name = x.n.value('local-name(.)', 'varchar(max)'), OldValue = x.n.value('.', 'varchar(max)') from @OldXML.nodes('/R/@*') x(n)) A
    full join (select Name = x.n.value('local-name(.)', 'varchar(max)'), NewValue = x.n.value('.', 'varchar(max)') from @NewXML.nodes('/R/@*') x(n)) B
         on A.Name = B.Name
    left join sys.extended_properties P on P.major_id = object_id(@TableName) and COL_NAME(P.major_id, P.minor_id) = isnull(A.Name,B.Name)
  where isnull(OldValue,'') <> isnull(NewValue,'')

  delete from @TmpJ where Id = @Id
end

Картинка с другого сайта.

Покртиткуйте пожалуйста подход сравнения, может можно сделать красивее и элегантнее
17 янв 17, 09:05    [20112971]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить