support
Support
Количество сообщений
1199
Зарегистрирован:
11-04-2008, 19:11:57
|
Импорт каталога из файла (XML)YML
Тип товара из тега typePrefix импортируется в отдельный справочник типов товаров и хранится отдельно, для возможного последующего использования (например экспорта в YML). Чтобы добавить его в начало наименования товара можете воспользоваться следующим скриптом:
Код
declare @xml xml
declare @idoc int
declare @FileName varchar(2000);
SET @FileName = 'C:\TEXP\price_yml.xml';
DECLARE
@SQL nvarchar(4000),
@SQL_Params nvarchar(4000);
--SET @SQL = 'SELECT @xml = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET (BULK N''' + @FileName + ''', SINGLE_CLOB) [rowsetresults]';
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 + ' SELECT @text = RIGHT(@text, LEN(@text) - CHARINDEX(''<?xml'', @text) + 1);';
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 + ' IF CHARINDEX(''<company><![CDATA['', @text)=0 SELECT @text = REPLACE(REPLACE(@text, ''<company>'', ''<company><![CDATA[''), ''</company>'', '']]></company>'');'
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;
--SET @xml = REPLACE(CAST(@xml as varchar(MAX)), 'encoding=''cp1251''', 'encoding="WINDOWS-1251"');
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 @GR_ID int, @iGR_PARENT_ID int, @GR_NAME varchar(400);
DECLARE cGroups CURSOR LOCAL FOR
select GR_ID, GR_PARENT_ID, GR_NAME from @TBL_GROUPS
;
SET IDENTITY_INSERT TBL_GROUPS ON;
OPEN cGroups;
FETCH NEXT FROM cGroups INTO @GR_ID, @iGR_PARENT_ID, @GR_NAME;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF NOT EXISTS (SELECT 1 FROM TBL_GROUPS WHERE GR_ID=@GR_ID)
BEGIN
INSERT INTO TBL_GROUPS (GR_ID, GR_PARENT_ID, GR_NAME) VALUES (@GR_ID, @iGR_PARENT_ID, @GR_NAME);
END;
FETCH NEXT FROM cGroups INTO @GR_ID, @iGR_PARENT_ID, @GR_NAME;
END;
SET IDENTITY_INSERT TBL_GROUPS OFF;
CLOSE cGroups;
DEALLOCATE cGroups;
DECLARE @GT_ID int, @B_ID int;
DECLARE @G_ID varchar(400), @G_NAME varchar(400), @G_URL varchar(400), @G_GR_ID int, @B_NAME varchar(400), @G_IMAGE_URL varchar(400), @G_DESC_FULL varchar(max), @G_MODEL varchar(100), @G_SID varchar(100), @GT_NAME varchar(100);
DECLARE cGoods CURSOR LOCAL FOR
SELECT
G_ID,
LTRIM(RTRIM(ISNULL(G_NAME, ''))),
LTRIM(RTRIM(ISNULL(G_URL, ''))),
G_GR_ID,
LTRIM(RTRIM(ISNULL(B_NAME, ''))),
LTRIM(RTRIM(ISNULL(G_IMAGE_URL, ''))),
G_DESC_FULL,
LTRIM(RTRIM(ISNULL(G_MODEL, ''))),
LTRIM(RTRIM(ISNULL(G_SID, ''))),
LTRIM(RTRIM(ISNULL(GT_NAME, '')))
FROM OPENXML (@idoc, N'/yml_catalog/shop/offers/offer')
WITH (
G_ID varchar(400) '@id',
G_NAME varchar(400) 'name/text()',
G_URL varchar(400) 'url/text()',
G_GR_ID int 'categoryId/text()',
B_NAME varchar(400) 'vendor/text()',
G_IMAGE_URL varchar(400) 'picture/text()',
G_DESC_FULL text 'description/text()',
G_MODEL varchar(100) 'model/text()',
G_SID varchar(100) 'vendorCode/text()',
GT_NAME varchar(100) 'typePrefix/text()'
) g
inner join @TBL_GROUPS_FULL gr on g.G_GR_ID = gr.GR_ID
where LTRIM(RTRIM(IsNull(G_NAME, ''))) <> ''
;
SET IDENTITY_INSERT TBL_GOODS ON;
OPEN cGoods;
FETCH NEXT FROM cGoods INTO @G_ID, @G_NAME, @G_URL, @G_GR_ID, @B_NAME, @G_IMAGE_URL, @G_DESC_FULL, @G_MODEL, @G_SID, @GT_NAME;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF NOT EXISTS (SELECT 1 FROM TBL_GOODS WHERE G_ID = @G_ID)
BEGIN
SET @GT_ID = Null;
IF @GT_NAME <> ''
BEGIN
SET @G_NAME = @GT_NAME + ' ' + @G_NAME
SET @GT_ID = (SELECT GT_ID FROM TBL_GOODS_TYPES WHERE GT_NAME = @GT_NAME);
IF @GT_ID IS NULL
BEGIN
INSERT TBL_GOODS_TYPES (GT_NAME) VALUES (@GT_NAME);
SET @GT_ID = (SELECT GT_ID FROM TBL_GOODS_TYPES WHERE GT_NAME = @GT_NAME);
END;
END;
SET @B_ID = Null;
IF @B_NAME <> ''
BEGIN
SET @B_ID = (SELECT B_ID FROM TBL_BRANDS WHERE B_NAME = @B_NAME);
IF @B_ID IS NULL
BEGIN
INSERT TBL_BRANDS (B_NAME) VALUES (@B_NAME);
SET @B_ID = (SELECT B_ID FROM TBL_BRANDS WHERE B_NAME = @B_NAME);
END;
END;
INSERT INTO TBL_GOODS (G_ID, G_GR_ID, G_NAME, G_MODEL, G_B_ID, G_GT_ID, G_SID) VALUES (@G_ID, @G_GR_ID, @G_NAME, @G_MODEL, @B_ID, @GT_ID, @G_SID);
END;
FETCH NEXT FROM cGoods INTO @G_ID, @G_NAME, @G_URL, @G_GR_ID, @B_NAME, @G_IMAGE_URL, @G_DESC_FULL, @G_MODEL, @G_SID, @GT_NAME;
END;
SET IDENTITY_INSERT TBL_GOODS OFF;
CLOSE cGoods;
DEALLOCATE cGoods;
DECLARE @P_ID int, @PG_ID int;
DECLARE @P_NAME varchar(400), @PG_NAME varchar(400), @GP_VALUE varchar(max);
DECLARE cProps CURSOR LOCAL FOR
select
LTRIM(RTRIM(ISNULL(p.G_ID, ''))),
LTRIM(RTRIM(ISNULL(p.P_NAME, ''))),
LTRIM(RTRIM(ISNULL(p.PG_NAME, ''))),
LTRIM(RTRIM(ISNULL(p.GP_VALUE, ''))),
gr.GR_FULL_NAME
FROM OPENXML (@idoc, N'/yml_catalog/shop/offers/offer/param')
WITH (
G_ID varchar(400) '../@id',
P_NAME varchar(400) '@name',
PG_NAME varchar(400) '@group',
GP_VALUE varchar(8000) 'text()'
) p, TBL_GOODS g, TBL_GROUPS gr
WHERE p.G_ID = g.G_ID and g.G_GR_ID = gr.GR_ID
;
OPEN cProps;
FETCH NEXT FROM cProps INTO @G_ID, @P_NAME, @PG_NAME, @GP_VALUE, @GR_NAME;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @P_NAME <> ''
BEGIN
SET @PG_ID = Null;
IF @PG_NAME <> ''
BEGIN
SET @PG_ID = (SELECT PG_ID FROM TBL_PROPS_GROUPS WHERE PG_NAME = @PG_NAME);
IF @PG_ID IS NULL
BEGIN
INSERT TBL_PROPS_GROUPS (PG_NAME) VALUES (@PG_NAME);
SET @PG_ID = (SELECT PG_ID FROM TBL_PROPS_GROUPS WHERE PG_NAME = @PG_NAME);
END;
END;
SET @P_ID = (SELECT P_ID FROM TBL_PROPS WHERE P_NAME = @P_NAME AND P_DESC = @GR_NAME AND ISNULL(P_PG_ID, -1) = ISNULL(@PG_ID, -1));
IF @P_ID IS NULL
BEGIN
INSERT TBL_PROPS (P_NAME, P_DESC, P_PG_ID) VALUES (@P_NAME, @GR_NAME, @PG_ID);
SET @P_ID = (SELECT P_ID FROM TBL_PROPS WHERE P_NAME = @P_NAME AND P_DESC = @GR_NAME AND ISNULL(P_PG_ID, -1) = ISNULL(@PG_ID, -1));
END;
IF NOT EXISTS(SELECT 1 FROM TBL_GOODS_PROPS WHERE GP_G_ID = @G_ID AND GP_P_ID = @P_ID)
BEGIN
INSERT INTO TBL_GOODS_PROPS (GP_G_ID, GP_P_ID, GP_VALUE) VALUES (@G_ID, @P_ID, @GP_VALUE);
END;
END;
FETCH NEXT FROM cProps INTO @G_ID, @P_NAME, @PG_NAME, @GP_VALUE, @GR_NAME;
END;
CLOSE cProps;
DEALLOCATE cProps;
Не забудьте, что пусть и имя файла C:\TEXP\price_yml.xml вам нужно заменить на свое.
|
