Новичок
Профиль
Группа: Участник
Сообщений: 20
Регистрация: 26.12.2014
Репутация: нет Всего: нет
|
Я пользуюсь софтом для чтения прайсов Эксель и xml, так вот xml там затягиваются через скрипт. Вот его код, может поможет: Код | declare @xml xml declare @idoc int declare @FileName varchar(2000); SET @FileName = :FILENAME;
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 + ' 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 @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 NAME = REPLACE(IsNull(CAST(NAME as varchar(8000)), ''), '(( ))', '');
update #TMP_PRICE set NAME = IsNull(LTRIM(RTRIM(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 DESC_FULL = null where LTRIM(RTRIM( CAST(NAME as varchar(8000)) )) = LTRIM(RTRIM( CAST(DESC_FULL as varchar(8000)) ));
update #TMP_PRICE set DESC_FULL = null where LTRIM(RTRIM( CAST(DESC_FULL as varchar(8000)) )) = '-';
|
|