Импорт товаров через XML файлы (YML, CML и др). Интеграция с 1С

Суть компонента импорта XML

На входе имеем файл (архив zip или xml). XML имеют некий формат  (CML, YML, RSS). 

Мы можем задать процедуру извлечения данных из файла, сохранить в промежуточные таблицы данные, а затем обновить данные в основных таблицах (например, в таблицах каталога). 

Этапы работы компонента

1. Компонент получает путь к файлу (локальный или URL) и формат XML.
2. Распаковываем архив во временную папку /uploads/importFiles/{guid}.
3. Из найденных во временной папке xml файлов по правилам, описанным в xml_{type}_getObjectsDefinition, извлекаем данные импорта.
4. Вызываем процедуру импорта xml_{type}_import, куда передаем данные импорта в виде json, username, и путь до временной папки с файлами импорта. Процедура сохраняет уже разобранные и обработанные данные и результат импорта в таблицы, которые дальше можно использовать в других процедурах для обновления данных в системе.
5. Через внешнее действие вызывается процедура xml_{type}_updateData, которая выполняет обновление данных в основных таблицах (используя данные, которые мы получили в рамках сессий импорта).  

Основные элементы компонента

• Таблица as_xml_types - форматы XML, к которым привязываются процедуры обработки данных getObjectsDefinition, import, updateData. 
• Таблица as_xml_importSessions - хранятся сессии выполненного импорта (лог операций по импорту).
• Таблицы вида as_xml_cat_… - это таблицы временного хранения для последующего обновления каталога. Для других типов импорта (не для каталога, а например, для финансов) необходимо создавать свои таблицы вида as_xml_fin_ ….
• getObjectsDefinition - хранимка получения атрибутов для типа XML 
• import - хранимка по обработке XML и сохранению данных во временных таблицах вида as_xml_cat_…
• updateData - хранимка обновления данных в пользовательских таблицах (например для каталога это таблица as_cat_...).

Процедура для описания импортируемых объектов xml_{type}_getObjectsDefinition

Процедура получает объекты и их параметры, находящиеся в XML файле. 

На выходе: 

declare @objectTypes table (
        [Name] nvarchar(100),
        [Path] nvarchar(800)
) 

declare @objectFields table (
        ObjectType nvarchar(100),
        [Name] nvarchar(100),
        [Path] nvarchar(800),
        IsMany bit,
        IsJson bit,
        IsPrimaryKey bit,
        IsRequired bit
)

 SELECT 1 objectTypes - типы импортируемых объектов

• Name - название сущности
• Path - XPath относительно xml-документа

SELECT 2 objectFields - поля объектов

• ObjectType - название типа объекта, связка с objectTypes
• Name - название поля
• Path - XPath относительно объекта
• IsMany - если 1, то в это поле при наличии может попасть несколько значений, разделенных через ||, иначе берем первое попавшееся
• IsJson - если 1, то поле вместе со всем вложенным содержимым сериализуем в json как есть. Можно использовать для сложных объектов. Обычно более удобный вариант - для этого сложного содержимого создать отдельную сущность и включить в него id родителя для связки.
• IsPrimaryKey - если 1, то по этому полю группируем и склеиваем в один объекты, собранные из разных xml файлов. Такое поле может быть только одно.
• IsRequired - если 1 и это поле пустое, то выбрасываем весь объект

Процедура импорта xml_{type}_import

Процедура для сохранения данных во временные таблицы вида as_xml_cat… (пример для каталога)

Входные параметры:

@data nvarchar(max) - json с данными импорта

@username nvarchar(128) - username

@sessionFolder nvarchar(512) - путь до временной папки, в которую были сохранены/распакованы xml файлы и картинки. Пути до картинок, полученные из xml, могут быть либо путями относительно этой папки, либо абсолютными url до оригинала на сайте клиента.

Выходной результат

SELECT 1: 

• result bit - 1, если импорт завершен успешно
• msg nvarchar(max) - сообщение об ошибке

SELECT 2: внешние действия.

Процедура обновления данных в пользовательских таблицах xml_{type}_updateData

Процедура сохраняет данные из временных таблиц в пользовательские таблицы. 

На входе: 

• logID - запись в логе обновления данных 
• Parameters - содержит itemID, который позволяет привязать данные к неким объектам в БД.
• username - текущий пользователь

На выходе: 

result, msg  - результат операции и выводимое сообщение. 

Важно. В процедуре должны быть обновлены данные по результату операции через 

update as_xml_updateLog
set result = @result, resultMessage = @msg
where id = @logID

Вызов импорта через внешние действия

importxml - импортирует данные во временные таблицы:

• path - путь к файлу  (архив zip или xml файл. Если начинается с http - то качается с удаленного сервера)
• xmltype - код формата XML (пример - cml, yml).
• itemIDForUpdateData nvarchar(256) - идентификатор, который передается в хранимку updatedata для связывания данных с нужным объектом в системе (например, поставщиком). Если строка непустая, то после сохранения таблиц сразу данные передаются дальше на сохранение в пользовательские таблицы.

importxml.updatedata - обновляет данные в пользовательских таблицах на основе данных из определенной сессии импорта. Параметры: 

• sessionID - ID сессии импорта  из таблицы as_xml_importSessions
• itemID - идентификатор для  связи данных с определенным объектом в базе (например, поставщик). 

Пример на основе RSS

RSS имеет подобный формат: 

<rss version="0.91">
<channel>
<title>XML.com</title>
<link>http://www.xml.com/
<description>XML.com features a rich mix of information and services for the XML community.</description>
<language>en-us</language>
<item>
<title>Normalizing XML, Part 2</title>
<link>http://www.xml.com/pub/a/2002/12/04/normalizing.html
<description>In this second and final look at applying relational normalization techniques to W3C XML Schema data modeling, Will Provost discusses when not to normalize, the scope of uniqueness and the fourth and fifth normal forms.</description>
<pubdate>Sun, 04 Apr 2021 22:17:04 -0500</pubdate>
</item>
<item>
<title>The .NET Schema Object Model</title>
<link>http://www.xml.com/pub/a/2002/12/04/som.html
<description>Priya Lakshminarayanan describes in detail the use of the .NET Schema Object Model for programmatic manipulation of W3C XML Schemas.</description>
<pubdate>Sun, 04 Apr 2021 22:17:04 -0500</pubdate>
</item>
<item>
<title>SVG's Past and Promising Future</title>
<link>http://www.xml.com/pub/a/2002/12/04/svg.html
<description>In this month's SVG column, Antoine Quint looks back at SVG's journey through 2002 and looks forward to 2003.</description>
<pubdate>Sun, 04 Apr 2021 22:17:04 -0500</pubdate>
</item>
</channel>
</rss>

2 основных элемента - channel и item. 

Шаг 1. Создаем XML тип rss. 

Шаг 2. Описываем параметры RSS в процедуре getObjectsDefinition

CREATE OR ALTER PROCEDURE [dbo].[xml_rss_getObjectDefinitions]
as
begin

    declare @result bit = 1, @msg nvarchar(1000) = ''

    declare @objectTypes table ( --типы сущностей, которые будем извлекать
        [Name] nvarchar(100),
        [Path] nvarchar(800) --xpath относительно документа
    )

    declare @customFields table ( --поля сущностей
        ObjectType nvarchar(100), --название сущности, связка с таблицей выше
        [Name] nvarchar(100),
        [Path] nvarchar(800), --XPath относительно сущности
        IsMany bit, --если 1, то в это поле при наличии может попасть несколько значений, разделенных через ||, иначе берем первое попавшееся
        IsJson bit, --если 1, то поле вместе со всем вложенным содержимым сериализуем в json как есть. Можно использовать для сложных объектов
                    --обычно более удобный вариант - для этого сложного содержимого создать отдельную сущность и включить в него id родителя для связки
        IsPrimaryKey bit, --если 1, то по этому полю группируем и склеиваем в один объекты, собранные из разных xml файлов. Такое поле может быть только одно
        IsRequired bit --если 1 и это поле пустое, то выбрасываем весь объект
    )

    insert into @objectTypes ([Name], [Path]) values
        ('channel', 'rss/channel'), --инфо о файле импорта
        ('item', 'rss/channel/item')

    insert into @customFields (ObjectType, [Name], [Path], IsMany, IsJson, IsPrimaryKey, IsRequired) values
        ('channel', 'title', N'title', 0, 0, 0, 0),
        ('channel', 'link', N'link', 0, 0, 0, 0),
        ('channel', 'description', N'description', 0, 0, 0, 0),
        ('channel', 'language', N'language', 0, 0, 0, 0),

        ('item', 'title', N'title', 0, 0, 0, 0),
        ('item', 'link', N'link', 0, 0, 0, 0),
        ('item', 'description', N'description', 0, 0, 0, 0),
        ('item', 'pubDate', N'pubDate', 0, 0, 0, 0)



    select * from @objectTypes
    select * from @customFields
end

Для каждого элемента прописываем путь к значению. Если это подэлемент, то просто используем имя. Если это атрибут, то указываем с @.

Шаг 3. Создаем временные таблицы для хранения значений выгрузки с привязкой к сессии (as_xml_rss_channels и as_xml_rss_items). 

Шаг 4. Создаем процедуру импорта: 

CREATE OR ALTER PROCEDURE [dbo].[xml_rss_import]
@data nvarchar(max), --json
@username nvarchar(128),
@sessionID int
as
begin

    declare @result bit = 1, @msg nvarchar(max) = '', @userID int
    declare @filesDate datetime

    begin try
        begin tran

            declare @channelID int

            insert into as_xml_rss_channels (sessionID, title, link, description, language)
            select @sessionId, *
            from openjson(@data, '$.channel') with (
                title nvarchar(100) '$.title',
                link nvarchar(100) '$.link',
                description nvarchar(100) '$.description',
                language nvarchar(100) '$.language'
            )
            set @channelID = scope_identity()

            insert into as_xml_rss_items (sessionID, channelID, title, link, description, pubDate)
            select @sessionId, @channelID, title, link, description, try_cast(pubDate as datetime) pubDate
            from openjson(@data, '$.item') with (
                title nvarchar(100) '$.title',
                link nvarchar(100) '$.link',
                description nvarchar(100) '$.description',
                pubDate nvarchar(100) '$.pubDate'
            )

			set @result = 1
            set @msg = 'Получено элементов ' + cast(isnull((select count(*) from as_xml_rss_items where sessionID =@sessionID), 0) as nvarchar)

        commit
    end try
    begin catch
        rollback
        set @result = 0
        set @msg = error_message() + '. Line: ' + cast(error_line() as nvarchar)


    end catch

	update as_xml_importSessions
	set resultMessage = @msg, result = @result
	where id = @sessionID


    select @result Result, @msg Msg
end

Примечание: 

• Для RSS используется 1 channel. Если бы было много элементов channel, то в параметрах item мы бы извлекали parenID параметр, который имел бы некий идентификатор канала. Этот параметр мы использовали бы для связи между отдельным каналом и элементом. 
• В конце процедуры мы указываем в Session результат операции (установка полей result, resultMessage)
• Если что-то пойдет не так, вся транзакция будет отменена
• Данные приходят в виде JSON массива объектов, который легко разбирается по атрибутам. 

Шаг 5. Создаем процедуру обновления данных (из временных таблиц в рабочие)

CREATE OR ALTER PROCEDURE [dbo].[xml_rss_updateData]
@logID int,
@parameters ExtendedDictionaryParameter readonly,
@username nvarchar(128)
as
begin
	declare @result bit =0, @msg nvarchar(max) = '', @itemID nvarchar(256) = '', @sessionID int
	select @sessionID = sessionID from as_xml_updateLog where id = @logID

	select @itemID = isnull(Value2, Value) from @parameters where lower([Key]) ='itemid'

	-- вставляем данные в конечную таблицу (но только те, что отличаются)

    begin try
        begin tran
        	declare @t table (id int)

            insert into @t(id)
          	select id from as_xml_rss_items  ri
	        where sessionID  = @sessionID
    	          and not exists (select id from tmp_xml_rssItems where title = ri.title)

          insert into tmp_xml_rssItems(title,link, description, pubDate)
          select title, link, description, pubDate from as_xml_rss_items  ri
          where id in (select id from @t)

          set @result  = 1
          set @msg = 'new  items  -' + cast((select count(*) from @t) as nvarchar)
        commit
    end try
    begin catch
        rollback
        set @result = 0
        set @msg = error_message() + '. Line: ' + cast(error_line() as nvarchar)
    end catch

	update as_xml_updateLog
	set result = @result, resultMessage = @msg
	where id = @logID

    select @result Result, @msg Msg
end

Шаг 6. Используем внешнее действие importxml в форме для выполнения операции импорта. 

При этом файлы могут забираться либо с удаленного сервера, либо из папки приложения (куда они могли быть загружены менеджером ресурсов или через FTP). 

Полезные возможности XPath

Рекурсивный поиск объектов (//)

Включает в поиск не только дочерние элементы объекта, но и все потомки

Полезно для объектов, которые могут быть вложены друг в друга, например, для категорий

Пример:

Классификатор/Группы//Группа

Находит не только группы внутри Классификатор/Группы, но и группы внутри других групп

Переход на уровень вверх (..)

Позволяет включить в объект элемент родительского элемента, например, id

Пример:

../../Ид

Использование атрибутов (@)

Символ @ перед названием элемента означает, что элемент является атрибутом.

Пример:

Корень документа является тегом

<КоммерческаяИнформация ВерсияСхемы="2.05" ДатаФормирования="2020-10-05T10:55:16">

XPath объекта:

КоммерческаяИнформация

XPath поля с датой:

@ДатаФормирования

Считывание значения поля из корня объекта (.)

Иногда значение поля для объекта - это текст внутри тега самого объекта.

Пример (характеристика товара в yml):

Тег:

<param name="Цвет">белый

Объект:

('characteristic', 'yml_catalog/shop/offers/offer/param')

Поля

('characteristic', 'name', N'@name', 0, 0, 0, 0),

('characteristic', 'value', N'.', 0, 0, 0, 0),

('characteristic', 'productId', N'../@id', 0, 0, 0, 0),

Путь до значения поля value указываем в виде точки, что означает, что элемент этого поля и элемент всего объекта - один и тот же элемент, и текст берем прямо из него.

Работа с данными в JSON

Структура JSON определяется типами объектов, описанными в xml_{type}_getObjectsDefinition

и в общем виде выглядит так:

{
    "objectType1": [
        {
            "field1": "value1",
            "field2": "value2"
        }
    ],
    "objectType2": [
        {
            "field1": "value1",
            "field2": "value2"
        }
    ]
}

Где objectType - тип объекта

Field - название поля

Value - значение поля

Объекты всегда находятся в списках, даже если мы не планируем извлекать более одного.

Извлечение значения в одном экземпляре

Пример:

declare @fileDate datetime = json_value(@data, '$.general[0].date')

general - название типа объекта

[0] - берем первый объект в списке (в данном случае он должен быть всего один)

date - название поля

Извлечение таблицы

Пример сборки временной таблицы из json:

select [id],[name],[article],[description],[groupId],[images]
into #cml_products
from openjson(@data, '$.product') with (
    [id] nvarchar(100) '$.id',
    [name] nvarchar(200) '$.name',
    [article] nvarchar(200) '$.article',
    [description] nvarchar(4000) '$.description',
    [groupId] nvarchar(100) '$.groupId',
    [images] nvarchar(100) '$.images'
)

В openjson(@data, '$.product') указываем название объекта

Внутри with описываем поля: название + тип данных + путь до поля внутри json (названия совпадают с теми, которые мы указали в процедуре с описанием типов)

Дальше можем данные из временной таблицы использовать, чтобы получить конечный результат.

Всё это стоит делать внутри транзакции, чтобы не импортировать неполные данные.

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