Обработка значений из @DiffXml

@DiffXml используется для получения данных из полей, в которые были внесены изменения в результате:

• Добавления новой записи.

• Удаления записи.

• Изменения существующей записи.

Подробнее о работе с данными типа XML в MS SQL можно почитать в документации Microsoft.

Добавление новой записи

<Record>
	<Title>Преступление и наказание</Title>
	<Author>Федор Достоевский</Author>
	<Price>485</Price>
	<Order_o2m>
		<Record>
			<DateRegistration>06/01/2023 15:32:12</DateRegistration>
			<Quantity>500</Quantity>
			<Comment>Регулярный заказ</Comment>
		</Record>
		<Record>
			<DateRegistration>06/08/2023 15:32:37</DateRegistration>
			<Quantity>500</Quantity>
			<Comment>Промо акция</Comment>
		</Record>
	</Order_o2m>
</Record>

• Вся запись всегда хранится в теге Record.

• Каждый тег, вложенный в Record, соответствует полю объекта, в котором изменяются записи.

• В теге логического поля вложено столько тегов Record, сколько записей было изменено в объекте, на который это поле ссылается (<Order_o2m> — логическое o2m-поле).

Удаление записи

<Record destroy="true">
	<ID>3</ID>
</Record>

• Для удаляемых записей в Record указан атрибут destroy="true".

• В DiffXml попадает только ID удаляемой записи.

Изменение существующей записи

<Record>
	<ID>2</ID>
	<Price />
	<Author>Федор Михайлович Достоевский</Author>
	<Order_o2m>
		<Record>
			<ID_Book>2</ID_Book>
			<DateRegistration>04/19/2023 15:39:55</DateRegistration>
			<Quantity>150</Quantity>
		</Record>
	</Order_o2m>
</Record>

• В @DiffXml попадают только:

  • ID изменяемой записи.

  • Поля, данные в которых были изменены (в данном примере Title не был изменен ).

• При попытке получить значение из тега, которого нет в @DiffXml с помощью метода xml.value() вернется null.

  • В примере: @DiffXml.value('(/Record/Title)', 'varchar(255)') вернет null.

• <Price /> означает, что значение в этом поле было изменено на null.

  При получении значения с помощью метода xml.value() из тега, значение в котором было изменено на null, вернется значение по умолчанию для данного типа данных.

  В примере: @DiffXml.value('(/Record/Price)', 'int') вернет 0.

  Значения по умолчанию для используемых типов данных:

  Маска	Число
  datetime	1900-01-01 00:00:00.000
  date	1900-01-01
  time	00:00:00
  bit	0
  int	0
  decimal(18, 2)	0.00
  varchar(n)	Пустая строка: (' ')

Изменение записей в объекте, на который ссылается логическое поле

• При изменении существующей записи, в тег Record записывается ID этой записи и теги всех полей, в которых произошли изменения.

• При удалении записи, у тега Record появляется атрибут destroy со значением true.

• Для удаляемой записи в теге Record записаны теги всех полей объекта с соответствующими значениями (включая системные поля MDT).

• Для новой записи в теге Record отсутствует ID, т.к. он еще не присвоен.

Парсинг @DiffXml

Используется для обработки одновременного изменения нескольких записей в объекте, на который ссылается логического поле. Результат данного запроса можно, например, записать во временную таблицу для дальнейшего использования в рамках процедуры.

select
	d.n.value('(ID)[1]', 'int') as ID_Book
	/*
		Добавляем флаги, с помощью которых можно будет фильтровать записи по категориям:
		"новые записи" - FlagNew = 1, FlagDeleted = 0,
		"измененные записи" - FlagNew = 0, FlagDeleted = 0,
		"удаленные записи" - FlagNew = 0, FlagDeleted = 1
	*/
	,iif(epm.ID is not null, 0, 1) as FlagNew
	,iif(d.n.value('(@destroy)[1]', 'varchar(255)') is null, 0, 1) as FlagDeleted
	-- Получаем данные по каждому интересующему полю
	,case isnull(d.n.value('(DateRegistration)[1]', 'date'), o.DateRegistration)
		-- Если первый аргумент возвращает значение по умолчанию, обрабатываем как "null"
		when cast('1900-01-01' as date)
			then null
		-- В противном случае выбираем первое значение отличное от "null"
		else isnull(d.n.value('(DateRegistration)[1]', 'date'), o.DateRegistration)
	end as DateRegistration
	,case isnull(d.n.value('(Quantity)[1]', 'int'), o.Quantity)
		when 0
			then null
		else isnull(d.n.value('(Quantity)[1]', 'int'), o.Quantity)
	end as Quantity
	,case isnull(d.n.value('(Comment)[1]', 'varchar(8000)'), o.Comment)
		when ''
			then null
		else isnull(d.n.value('(Comment)[1]', 'varchar(8000)'), o.Comment)
	end as Comment
-- Получаем набор строк, соответствующих каждому Record-у, вложенному в логическое поле
from @DiffXml.nodes('/Record/Order_o2m/Record') as d(n)
	-- Объединяем для получения данных о тех полях существующих записей, которые не были изменены
	left join onboarding.[Order] as o on o.ID = d.n.value('(ID)[1]', 'int')

Отлов @DiffXml в БД

Чтобы отловить @DiffXml в БД, нужно:

1. Создать таблицу (например dbo.DiffXml) с полем типа XML.

   drop table if exists dbo.DiffXml
   go
   create table dbo.DiffXml (
   	ID int null
   	,DiffXml xml null
   )

2. Перезагрузить объекты в MDT, т.к. появилась новая таблица.

3. В SQL обработчике написать инструкцию на заполнение таблицы значением из переменной @DiffXml.

   create or alter procedure rds.usp_AfterSavePassForBrigadier
   	@ID int
   	,@DiffXml xml
   as
   set xact_abort on
   set nocount on
   begin try
   		insert into dbo.DiffXml (ID, DiffXml)
   		select
   			@ID
   			,@DiffXml
   end try
   begin catch
   	;throw
   end catch

4. Зайти в MDT и сохранить запись, предварительно изменив в ней значения.

5. Просмотреть, какой XML приходит с помощью select из таблицы.

   select *
   from dbo.DiffXml