Yandex YML integration. Import-export a product catalog in YML

General information about YML

Introduction to YML

This document describes working with data in YML format.

YML (Yandex Market Language) —a standard developed by Yandex for accepting and publishing information in the Yandex database.Market. When using a YML file, the catalog is regularly updated on Yandex.Market will occur automatically and reflect all current changes (availability, price, and new products).

                The unified format allows you to quickly and efficiently process information and place it in the directory Yandeks.Market.

                The YML standard is based on XML (Extensible Markup Language) — (XML DTD).

Brief description of the YML file format

Below is a brief description of the elements present in the generated YML file. A detailed description is available at the following links:

 https://yandex.ru/support/partnermarket/export/yml.html#yml-format

https://yandex.ru/support/partnermarket/elements/shop.html

https://yandex.ru/support/partnermarket/offers.html

https://yandex.ru/support/partnermarket/elements/param.html

 

The YML file consists of General information about the store and a list of product offers.

 

Main elements of YML

The element

Description/Example

xml header

Standard XML header. Must start from the first line, with a null character.

Acceptable YML file encodings: UTF-8, windows-1251.

Example :

yml_catalog

Any XML document can contain only one root element. The YML format uses the element as the root element. The date attribute of the element must match the date and time when the YML file was generated on the store's side. The date must be in the format YYYY-MM-DD hh:mm.

shop

The element contains a description of the store (shop), its offers and promotions (promos, gifts)*

 

*! Attention.: In our example, the promos and gifts sections are not generated or processed when parsing the file.

 

 

Description of elements in the shop section.

Description of elements included in the shop section

The element

Description/Example

Commitment

name

The short name of the shop.

<name>BestSellername>

Necessarily

company

Full name of the company that owns the store

<company>Tne Best inc.company>

Necessarily

url

URL of the store's main page. The maximum link length is — 512 characters.

<url>http://best.seller.ruurl>

Necessarily

currencies

The currencies element lists the store's currency exchange rates. Each currency is described by a separate currency element.

<currencies>

<currency id="RUR" rate="1"/>

currencies>

 

Necessarily

categories

List of store categories.

In YML format, the categories parent element contains a list of store categories. Each category is described by a separate category element.

<categories>

<category id="1">Книгиcategory>

<category id="2" parentId="1">Детективыcategory>

categories>

Necessarily

 

 

Description of elements in the offers section.

Description of elements included in   the offers section

The element

Description/Example

Commitment

id

The offer identifier. The maximum length is 20 characters. Must be unique for each offer.

in YML is an attribute for offer.

Necessarily

name

 The full title of the proposal.

Necessarily

url

URL of the product page on the store's website.

The maximum link length is — 512 characters

Necessarily

price

The actual price of the goods.

The separator of the integer and the fractional part of — point

Necessarily

currencyId

 

The currency in which the product price is indicated: RUR, USD.

the Price and currency must match each other.

Necessarily

categoryId

! Attention. Only used in YML format.

product category ID assigned by the store (integer, no more than 18 characters).

Necessarily

description

Description of the offer. The maximum text length is 3000 characters (including punctuation marks).

 

Not necessarily

param

 

 

All important product characteristics — color, size, volume, material, weight, age, gender, etc.

In YML, the offer element can contain multiple param elements (one param element — one characteristic).

white

 

 

 

Checking the YML file

You can check the received files using the XML feed Validator service.

Document validation Scheme – Market.

The link  - https://webmaster.yandex.ru/tools/xml-validator/

The verification results must be:

XML corresponds to the XSD schema.

 

 

Пример файла YML

<!--?xml version="1.0" encoding="UTF-8"?-->

<yml_catalog date="2019-11-01 17:22">

<shop>

<name>BestSeller</name>

<company>Tne Best inc.</company>

<url>Error! Invalid hyperlink object.&gt;

 <currencies>

<currency id="RUR" rate="1"></currency>

</currencies>

<categories>

<category id="1">Home apliances</category>

<category id="10" parentid="1">Small kitchen appliances</category> </categories>



 <offers>

<offer id="9012">

<name>Freezer Brand 3811</name> <url>http://best.seller.ru/product_page.asp?pid=12345</url>

<price>8990</price>

<currencyid>RUR</currencyid>

<categoryid>10</categoryid>

<param name="Color">white

<dimensions>20.1/20.551/22.5</dimensions> </offer>

</offers>

<gifts> <!-- gifts not from the price list --> </gifts>

<promos> <!-- promo action --> </promos>

</url></shop>

</yml_catalog>

 

 

Uploading to a file

Implemented functionality for uploading data from the Falcon database to a YML file.

Information is downloaded from the following database tables:

  • [as_cat_categories] Categories in the section <categories>
  • [as_cat_products] Products in the section <offers>
  • [as_cat_productAttrs] + [as_cat_filters] Parameters in the section <param >.

 

The following fields are loaded from the table [as_cat_categories]:

[id], [name], [parentID].

The following fields are loaded from the table [as_cat_products] :

[id], [name], [price],'RUR' AS currencyId, [url], [desc], categoryID.

The following fields are loaded from the table [as_cat_filters] :

[title].

The following fields are loaded from the table [as_cat_productAttrs] :

[value],  [intValue], [decimalValue].

 

 

Unloading is performed by the following procedures:

  1. [as_cat_getYMLFilterValue]
  2. [as_cat_products_getYMLHeaderData].

 

 

Procedure [as_cat_getYMLFilterValue]

Input parameter:

  @productID и @filterID.

The result:

 returns a line of the form white.

Exit:

Called in the procedure [as_cat_products_getYMLHeaderData] in the cursor.

Examples of a call:

EXECUTE [dbo].[as_cat_getYMLFilterValue] @productID= 10030  , @filterID = 91

 

Procedure [as_cat_products_getYMLHeaderData]

Input parameter:

Variable

Data type

Default

Note:

Required parameters YML file

@shopName

nvarchar(max)

'BestSeller'

- The short name of the shop.

@shopCompany

nvarchar(max)

'Tne Best inc.'

- Full name of the company that owns the store.

@shopURL

nvarchar(512)

'http://best.seller.ru'

- URL of the store's main page

Additional parameters YML file

@isUploadCategorys

bit

0

1 - pass the category structure from as_cat_categories

@isUploadAttrs

bit

0

1 - upload product attributes to the param section

 

The procedure via the additional parameters @isUploadCategorys and @isUploadAttrs allows you to adjust the content of the YML file. including loading/not loading categories from the [as_cat_categories] table.

And upload/not upload product attributes from the table [as_cat_productAttrs].

 

Result:

                Outputs data in YML file format.

Call:

                Manually, if necessary.

Call examples:

EXECUTE [dbo].[as_cat_products_getYMLHeaderData] 

  @shopName='TEST_Seller'

, @shopCompany= 'The TEST inc.'

, @shopURL = 'http://TEST_Seller.seller.ru'

, @isUploadCategorys =1

, @isUploadAttrs =1

 

As a result we have a tool that outputs formatted data that fully meets the YML file specification and passes validation.

 

The disadvantage of the [as_cat_products_getYMLHeaderData] process is that the resulting file text must be manually saved to a file on disk.

! Note: to work correctly, the file format must be UTF-8 !

Procedure [as_cat_products_getYML_File]

Procedure [as_cat_products_getYML_File] runs the above procedure [as_cat_products_getYMLHeaderData] and creates a file on disk with the results of this procedure.

It is essentially a wrapper for calling a procedure and creating a file. No other actions are performed.

Technically the procedure starts a system stored procedure master..xp_cmdshell  и SQLCmd —   утилита, входящая в состав SQL Server, предназначеная для выполнения скриптов Transact-SQL из командной строки.

 

Input parameter:

Variable

Data type

Default value

Note

Required parameter

@outputFilePath

NVARCHAR(1000)

Not

 path to the result file (without specifying the file name)

 

The other required parameters ,@shopName ,@shopCompany ,@shopURL ,@isUploadCategorys ,@isUploadAttrs, are shown in the table above.

The output file name format is formed using a template  out_YYYYMMDDTHH_mm.xml,

Where out_ is the constant part, and. xml is the extension.YYYYMMDDTHH_mm-template for the date and time of formation.

Example of a file name: out_20200413T14_42.xml

 

Access right:

You may need to configure the server once to run xp_cmdshell, see the link for description:

Server configuration parameter «xp_cmdshell»

https://docs.microsoft.com/ru-ru/sql/database-engine/configure-windows/xp-cmdshell-server-configuration-option?view=sql-server-ver15

Результат:

                Creates it using the specified path @outputFilePath  file with the name in the format out_YYYYMMDDTHH_mm.xml.

                Return value: 0 Success or 1 Failure.

Вызов:

                Manually, if necessary.

Call examples:

EXEC [dbo].[as_cat_products_getYML_File] @outputFilePath ='E:\' , @isUploadCategorys = 1 , @isUploadAttrs = 0

! Note: for correct operation, the file format must be UTF-8!

! Note: The encoding of the result file will not be checked. After creating the file, you need to check the encoding and, if necessary, re-save the file in the desired folder UTF-8.

(You can resave the file in notepad.)

Links:

Service program sqlcmd

https://docs.microsoft.com/ru-ru/sql/tools/sqlcmd-utility?view=sql-server-ver15

 

Parsing the YML file and updating data in the database.

There are also procedures for parsing the YML file and making changes/new data in the Falcon database.

Procedure [as_cat_parsingYML]

UPD: 2020-07-10

The procedure analyzes the contents of the received XML-type variable and, depending on the incoming parameters, can output diagnostic information on the received data or make changes to the database tables.

 

Input parameter:

Variable

Data type

Default value

Note

Required parameter

@XML_input

xml

No

the input parameter is a variable of type XML

@isUpdateDB

bit

0

whether to make changes to the database or to diagnose the file

0 - reading the file only for diagnostics

1 - making changes to the database from a file

@isUpdateAttrs

bit

1

reads/updates as_cat_productAttrs attributes

1-works with attributes

0-skip working with attributes

Optional parameter

@param_username

nvarchar(256)

NULL User name, if omitted, we take the server CURRENT_USER

@isUpdateCategoriesForAdminONLY

bit

0 When set to = 1, it allows you to change/add categories

!Note: параметр @isUpdateAttrs (attribute processing) works if @isUpdateDB=1.

!Note: parameter @param_username – used to determine the Supplier-via the ctr_contacts.USERNAME field - this is the username of the user in the system.

If database [as_cat_suppliers] if the supplier is found, the update will be executed/insert in the table. [as_cat_supplierProducts]. 

!Note: parameter - @isUpdateCategoriesForAdminONLY not available from the interface – since the supplier can't change the categories in any way.

 

Algorithm and working conditions.

The procedure reads data from the BX. An XML-type variable. The data is assumed to contain information from the YML file.

The information may contain the following data:

  • Categories         раздел <categories>     табл.БД [as_cat_categories]
  • Products         раздел <offers>         табл.Database [as_cat_products]
  • Product parameters раздел <param >       табл.БД [as_cat_productAttrs]

 

Since we sync different databases, the ID of the same products or categories in different databases may be different. To get a link between external system IDs, a field has been added to the [as_cat_categories] and [as_cat_products] tables outerID nvarchar(64).

 

If the as_cat_ymlimportlog and as_cat_YMLImportLogData tables exist in the database, they will record input data and the result of the procedure.

In the future, the results log will be generated from these tables (for example, when importing from a file).

Possible errors (entered in the table as_cat_YMLImportLog):

  • 'Error! The line yml_catalog in the file IS not defined!'
  • 'Error! The database does not have a field for comparing the outerID in table as_cat_categories!!'
  • 'Error! The database does not have a field for comparing the outerID in table as_cat_products!'

Before starting the procedure, you must check whether the field is available and filled in outerID!

 

When reading data, the record ID is read (in the section <categories> or <offers>).

The resulting ID is checked against the values in the outerID field.

If the outerID field contains a record with the same ID, the record is updated.

If the outerID field does not contain the required ID, a new data row is inserted.

 

Also, to correctly update or insert new attribute data (the as_cat_productAttrs table), you must have same (in the database data receiver and database source) the following  database object:

  1. The table as_cat_filters - a list of all the attributes. If this table does not contain an attribute field that was read from the file, the following message is output:

ERROR! The reference list (as_cat_filters) for the attribute : Manufacturer for offerID is not defined: 1.

 (entered in the table as_cat_YMLImportLogData)

  1. The table as_geo_regions - if the reference list of localities is used.
  2. all customized reference tablesas_cat_cust_ ...
  3. all customized procedures cat_filter_get_ ...

 

The following database tables can be updated:

  • [as_cat_categories] Data from the section <categories>
  • [as_cat_products] Data from the section <offers>
  • [as_cat_productAttrs] Data from the section <param >

 

Result:

                parsing input data and updating database tables

                the procedure outputs a string with general parameters and a detailed log of completed operations. Examples of output results will be discussed below.

 

Operating mode:

The procedure can be run in 2 modes:

1 – outputs only diagnostic information for the received data

2 – reads the received data and makes changes to the database tables.

 

Example of calling in diagnostic mode:

In this mode you only need to pass XML data.

DECLARE @xml xml

SELECT @xml = CAST(x.data as XML)

FROM OPENROWSET(BULK 'E:\YML_realty_ALL.XML', SINGLE_BLOB) AS x (data)

EXECUTE [dbo].[as_cat_parsingYML] @xml

Example of result in diagnostic mode:

 Diagnostics:

yml_catalog: Yes;

shop: Yes;

categories: Yes;

offers: Yes;

offerONE: Yes;

promos: No;

gifts: No;

param: Yes;

 

ATTRIBUTE - № category: 1;

ATTRIBUTE - № subcategory: 1;

ATTRIBUTE - № offer (Product): 1;

 

Categories found in the file:21

Updating Categories in the database: none;

 

Products found in the file:7

Updating Products in the database: no;

 

the log of completed operations – is empty, because no operations were performed with the database.

Example of a call в режиме database changes:

DECLARE @xml xml

SELECT @xml = CAST(x.data as XML)
FROM OPENROWSET(BULK 'E:\out_20200417T19_51_ATTRIB_1_UTF-8.xml', SINGLE_BLOB) AS x (data) 

EXECUTE [dbo].[as_cat_parsingYML] @xml , @isUpdateDB = 1, @isUpdateAttrs = 1

 

Example of the result of calling in database changes

Diagnostics:

yml_catalog: Yes;

shop: Yes;

categories: Yes;

offers: Yes;

offerONE: Yes;

promos: No;

gifts: No;

param: Yes;

ATTRIBUTE - № categories: 1;

ATTRIBUTE - № subcategory: 1;

ATTRIBUTE - № offer (Products): 1;

Categories found in the file:21

Updating Categories in the database: Yes;

Added  Categories: 0

Updated Categories: 21

Products found in the file:7

Updating Products in the database: Yes;

Added  Products: 0

Updated products: 7

 

Parameters: Products found in the file:7

Parameters: total number of lines:23

Parameters: NULL строк:0

 

Parameters: Added entries: 0

Parameters: Updated entries: 23

 

 log of completed operations

operationName

dbTableName

itemID

outerID

notes

UPDATE

as_cat_products

11495

1014

NULL

UPDATE

as_cat_productAttrs

11489

1

filterID 1 Producer paramText: Nokia

UPDATE

as_cat_productAttrs

11489

1

filterID 2 Platform paramText: Android

UPDATE

as_cat_productAttrs

11489

1

filterID 3 Screen size paramText: 5.40

UPDATE

as_cat_productAttrs

11489

1

filterID 4 Power, W paramText: 225

UPDATE

as_cat_productAttrs

11489

1

filterID 5 Wireline paramText: Да

UPDATE

as_cat_productAttrs

11490

2

filterID 1 Producer paramText: Nokia

UPDATE

as_cat_productAttrs

11490

2

filterID 2 Platform paramText: IOS

UPDATE

as_cat_productAttrs

11495

1014

filterID 11 Brand of special equipment paramText: Kubota

Log of completed operations with an error. Example: Log of file import results-Data 

#

Error

Error text

Operation

The database table

Product code

External code

Note

13

Нет

Without errors

UPDATE

as_cat_productAttrs

11107

10008

filterID 93 Country paramText: Russin Federation

14

ОШИБКА!!

ERROR! The reference list (as_cat_filters) is not dafined for the attribute: Address2 offerID: 10013 paramText: Baklanov street.

ERROR

as_cat_filters

 

10013

 

15

Нет

Without error

UPDATE

as_cat_productAttrs

11109

11094

filterID 16 ParamText Operation type: Sale

 

Working with categories

Since the supplier can't change categories in any way, automatic filling of categories from the file is disabled ( @isUpdateCategoriesForAdminONLY= 0).

If a product has a category not defined in our reference list (this is the OuterID field in our category list), then this product is assigned the categoriz – Miscellaneous. (A category with this name should be in the reference list.)

If and categories Different no file import results in the directory Log-Data – an SQL error will be recorded.

Журнал результатов импорта файла - Данные 1

Журнал результатов импорта файла - Данные 2

Procedure [as_cat_parsingYMLbyFile]

Procedure [as_cat_parsingYMLbyFile] complements the above procedure [as_cat_parsingYML].

Since procedure [as_cat_parsingYML] gets an XML-type variable as an input parameter, so it is not possible to read data directly from the file.

 

Procedure [as_cat_parsingYMLbyFile] reads data from the specified file and passes it for further parsing in the procedure [as_cat_parsingYML].

 

Input parameter:

Variable

Data type

Default

Note

@XMLFilePath

NVARCHAR(1000)

no

 file path

@isUpdateDB

bit

0

whether to make changes in the database or file diagnostics

0-read the file only for diagnostics

1-making changes to the database from the file

@isUpdateAttrs

bit

1

reads/updates as_cat_productAttrs attributes

1-works with attributes

0-skip working with attributes

! Note: additional access rights to the BULK operation are required to run!

Permission is required to use the BULK parameterADMINISTER BULK OPERATIONS or ADMINISTER DATABASE BULK OPERATIONS.

Result:

                The result is completely similar to the result of the procedure [as_cat_parsingYML].

Call:

                Manually, if necessary.

Call examples:

EXECUTE [dbo].[as_cat_parsingYMLbyFile] @XMLFilePath = 'E:\out_20200417T19_51_ATTRIB_1_UTF-8.xml' ,

                                        @isUpdateDB_input = 1, @isUpdateAttrs_input = 1

Procedure [as_cat_parsingYML_STR]

Процедура [as_cat_parsingYML_STR] differs from the previous procedure [as_cat_parsingYML] input parameter data type.

Процедура [as_cat_parsingYML_STR] receives input data as a line.

Converts a string to XML and parses the received data in the same way as the procedure [as_cat_parsingYML].

 

! Note: no additional access rights are required to run.

 

Mandatory requirements for the input line.

  1. The data type must be Nvarchar(max)
  2. A Unicode line must start with N'
  3. 1st line-Declaration XML encoding must be changed from "UTF-8" to "UTF-16" (this operation is performed inside the procedure).

 

Input parameter:

                 @xml_STR Nvarchar(max) – a variable of type Nvarchar(max)

Results:

                parsing input data and updating database tables

                output the result in text format

Call:

 Manually, if necessary.

Call examples:

DECLARE @xml_str NVarchar(max)

SET @xml_str = N'
EXECUTE [dbo].[as_cat_parsingYML_STR] @xml_str

Example of the result:

Received a line of characters: 188693

changing the encoding in the 1st line: "UTF-8" == > "UTF-16":

converting a line to xml

yml_catalog: Yes; shop: Yes; categories: Yes; offers: Yes; offerONE: Yes; promos: No; gifts: No

ATTRIBUTE - № categories: 1; ATTRIBUTE - № subcategories: 0; ATTRIBUTE - № offer (Product): 1

Treatment Categories: The Beginning

Categories found in the file:6

Added  Categories: 6

 ID: 2000 2001 2002 2003 2004 2005

Updated Categories: 0

 ID:

Processing Categories: The end

Processing Categories: The begin

Products found in the file:131

Added  products: 131

 ID: 10000 10001 10002 10003 10004 10005 10006 10007 10008 10009 10010 10011 …

Updated products: 0

 ID:

Processing Categories: The end

The conclusion

This document describes the full cycle of working with the YML format in the Falcon database.

Additionally

https://habr.com/ru/post/216985/

https://webmaster.yandex.ru/tools/xml-validator/

 

Note