support
Support
Количество сообщений
1199
Зарегистрирован:
11-04-2008, 19:11:57
|
Импортировать прайс-лист для Яндекс-Маркет
Прайс-листы XML импортируются через SQL-запрос импорта, который мжет обработать большинство ошибок которые встречаются в таких файлах. Приведенный ниже SQL-запрос импорта прайса подходит для загрузки 90% прайс-листов YML. В редких случаях, его нужно изменить, если коды категорий товаров и коды товаров являются не числом, а GUID или какой-либо текстовой строкой. Иногда нужно подкорректировать учет курса валюты (не умножать, а делить) или не учитывать признак наличия (avalable="true").
Код
declare @xml xml
declare @idoc int
declare @FileName varchar(2000);
SET @FileName = :FILENAME;
DECLARE
@SQL nvarchar(4000),
@SQL_Params nvarchar(4000);
SET @SQL = 'DECLARE @text varchar(MAX);';
SET @SQL = @SQL + ' SELECT @text = a FROM OPENROWSET (BULK N''' + @FileName + ''', SINGLE_CLOB) as result(a);'
SET @SQL = @SQL + ' SELECT @text = LTRIM(@text);';
SET @SQL = @SQL + ' IF CHARINDEX(''<?xml'', @text)<>1 SELECT @text = RIGHT(@text, LEN(@text) - CHARINDEX(''<?xml'', @text) + 1);';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''\"'', ''"'');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''cp1251'', ''WINDOWS-1251'');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''encoding="utf8"'', ''encoding="utf-8"'');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''encoding=''''utf8'''''', ''encoding=''''utf-8'''''');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''parentID'', ''parentId'');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, ''"'', ''"'');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, CHAR(7), '' '');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, CHAR(31), '' '');';
SET @SQL = @SQL + ' SELECT @text = REPLACE(@text, CHAR(1), '' '');';
SET @SQL = @SQL + ' SELECT @xml = CONVERT(xml, @text, 2);';
SET @SQL_Params = N'@xml xml output';
execute sp_executesql @stmt = @SQL, @params = @SQL_Params,
@xml = @xml output;
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
DECLARE @TBL_GROUPS TABLE (GR_ID int, GR_PARENT_ID int, GR_NAME varchar(MAX), GR_FULL_NAME varchar(MAX));
DECLARE @TBL_GROUPS_FULL TABLE (GR_ID int, GR_FULL_NAME varchar(MAX));
INSERT INTO @TBL_GROUPS (GR_ID, GR_PARENT_ID, GR_NAME)
SELECT distinct gr.*
FROM OPENXML (@idoc, N'/yml_catalog/shop/categories/category')
WITH (
categoryID int '@id',
parentId int '@parentId',
name nvarchar(600) 'text()'
) gr
WHERE not categoryID is null and not name is null;
update @TBL_GROUPS
set GR_PARENT_ID = null
where not GR_PARENT_ID in (select GR_ID from @TBL_GROUPS)
or GR_ID = GR_PARENT_ID;
WITH tree (data, id, level, pathstr)
AS (
SELECT GR_NAME, GR_ID, 0, CAST('' AS VARCHAR(MAX))
FROM @TBL_GROUPS
WHERE GR_PARENT_ID IS NULL
UNION ALL
SELECT GR_NAME, GR_ID, t.level + 1,
case when t.level = 0 then t.data + t.pathstr +' > '+ V.GR_NAME
else t.pathstr +' > '+ V.GR_NAME
end
FROM @TBL_GROUPS V
INNER JOIN tree t
ON (IsNull(t.id, 0) = IsNull(V.GR_PARENT_ID, 0))/* and t.level < 20*/)
INSERT INTO @TBL_GROUPS_FULL (GR_ID, GR_FULL_NAME)
SELECT /*SPACE(level*5) + data as data, */id, /*level, */case when IsNull(pathstr, '') = '' then data else pathstr end
FROM tree
ORDER BY case when IsNull(pathstr, '') = '' then data else pathstr end, id;
DECLARE @CURRENCY TABLE (CUR_CODE varchar(20), CUR_rate varchar(20));
INSERT INTO @CURRENCY (CUR_CODE, CUR_rate)
SELECT * FROM
OPENXML (@idoc, N'/yml_catalog/shop/currencies/currency')
WITH (
CUR_CODE varchar(20) '@id',
CUR_rate varchar(20) '@rate'
) c;
INSERT INTO #TMP_PRICE (PRESENT, SID, NAME, URL, PRICE, BRAND, IMAGE_URL, DESC_FULL, CURRENCY, CURRENCY_RATE, CATEGORY)
select * from
(
SELECT '1' as PRESENT, g.SG_ID,
case
when LTRIM(RTRIM(IsNull(g.SG_NAME, ''))) <> '' then g.SG_NAME
else case when CHARINDEX(RTRIM(LTRIM(IsNull(g.SG_BRAND, ''))), g.model) = 0 then LTRIM(IsNull(g.SG_BRAND, '') + ' ' + g.model) else g.model end
end as SG_NAME,
CASE when LTRIM(ISNULL(g.SG_URL, '')) <> '' then g.SG_URL else SG_URL2 end as SG_URL,
cast(g.SG_PRICE as varchar(30)) as SG_PRICE, LTRIM(RTRIM(IsNull(g.SG_BRAND, ''))) as SG_BRAND,
case when IsNull(g.SG_IMAGE_URL, '') <> '' then g.SG_IMAGE_URL else g.SG_IMAGE_URL2 end as SG_IMAGE_URL,
g.SG_DESC,
(
CASE
WHEN (c.CUR_CODE is null) AND (SELECT COUNT(*) FROM @CURRENCY) = 1 THEN
(SELECT CUR_CODE FROM @CURRENCY)
ELSE
c.CUR_CODE
END
) as SG_CURRENCY,
(
CASE
WHEN (c.CUR_rate is null) AND (SELECT COUNT(*) FROM @CURRENCY) = 1 THEN
(SELECT CUR_rate FROM @CURRENCY)
ELSE
c.CUR_rate
END
) as SG_CURRENCY_RATE, gr.GR_FULL_NAME
FROM OPENXML (@idoc, N'/yml_catalog/shop/offers/offer')
WITH (
SG_PRESENT bit '@available',
SG_ID varchar(400) '@id',
SG_NAME varchar(400) 'name/text()',
SG_URL varchar(400) 'url/text()',
SG_URL2 varchar(400) 'buyurl/text()',
SG_PRICE varchar(400) 'price/text()',
categoryId int 'categoryId/text()',
SG_BRAND varchar(400) 'vendor/text()',
SG_IMAGE_URL varchar(400) 'picture/text()',
SG_IMAGE_URL2 varchar(400) 'image/text()',
SG_DESC text 'description/text()',
SG_CURRENCY varchar(30) 'currencyId/text()',
-- SG_CURRENCY_RATE float '../../currency/@rate',
model varchar(100) 'model/text()'
) g
inner join @TBL_GROUPS_FULL gr on g.categoryId = gr.GR_ID
left outer join @CURRENCY c on g.SG_CURRENCY = c.CUR_CODE
where IsNull(SG_PRESENT, 1) = 1
) t
where LTRIM(RTRIM(IsNull(SG_NAME, ''))) <> '';
update #TMP_PRICE
set NAME = REPLACE(IsNull(CAST(NAME as varchar(8000)), ''), '(())', '');
update #TMP_PRICE
set NAME = REPLACE(IsNull(CAST(NAME as varchar(8000)), ''), '(( ))', '');
update #TMP_PRICE
set PRICE = REPLACE(IsNull(LTRIM(RTRIM(CAST(PRICE as varchar(8000)))), '0'), '.', ',');
update #TMP_PRICE
set CURRENCY_RATE = REPLACE(IsNull(LTRIM(RTRIM(CAST(CURRENCY_RATE as varchar(8000)))), '0'), '.', ',');
update #TMP_PRICE
set NAME = REPLACE(IsNull(LTRIM(RTRIM(CAST(NAME as varchar(400)))), ''), '()', '') where CAST(NAME as varchar(400)) like '%()%';
update #TMP_PRICE
set NAME = REPLACE(IsNull(LTRIM(RTRIM(CAST(NAME as varchar(400)))), ''), '( )', '') where CAST(NAME as varchar(400)) like '%( )%';
update #TMP_PRICE set NAME = REPLACE(IsNull(LTRIM(RTRIM(CAST(NAME as varchar(1000)))), ''), '"', '"');
Так же можно скачать готовую схему импорта в соответствующем разделе.
|
