Related Documents
Document Properties
Kbid
300D59
Last Modified
13-Jan-2022
Added to KB
27-Dec-2021
Public Access
Everyone
Status
Online
Doc Type
Guidelines, Concepts & Cookbooks
Product
ICM 7.10

Guide - 7.10.37.1 Cleanup of Leading and Trailing Custom Attribute Names

Table of Contents


Product Version

7.10.37

Product To Version


Status

Final

1 Introduction

This migration guide applies for 7.10.37.1, 7.10.32.8-LTS and 7.10.26.19-LTS.

Before version 7.10.37 it was possible to enter custom attribute names with leading and/or trailing spaces, either via ICM backoffice or via import. This could cause custom attributes to be duplicated. This behavior occurred on all entities that have custom attributes, e.g. products or categories.

Since version 7.10.37 it is no longer possible to create such custom attribute names. To clean up possibly existing custom attribute names, it it necessary to manually execute the SQL scripts provided with this migration guide.

2 Migration Steps

2.1 XML Custom Attributes

To migrate custom attributes which are stored as an XML structure, e.g. product custom attributes, the following scripts need to be executed, depending on the database system.

The scripts need to be executed for every custom attribute column, i.e. for the general column and each locale. For example, product could have the columns attributesattributes_en_us, attributes_de_de, etc.

The following placeholders need to be replaced:

  • table_name → the affected database table, e.g. PRODUCT
  • column_name → the affected column of the table, e.g. attributes_en_us

2.1.1 Oracle SQL

In Oracle SQL, the XML custom attributes are stored as text and can be updated via regular expressions.

Cleanup XML custom attribute names Oracle SQL
UPDATE table_name
SET column_name = REGEXP_REPLACE(column_name, '<attr name="[[:space:]]*([^[:space:]]{1,2}|[^[:space:]][^"]+[^[:space:]])[[:space:]]*">', '<attr name="\1">')
WHERE column_name IS NOT NULL;

Example:

Cleanup XML custom attribute names Oracle SQL example
UPDATE PRODUCT
SET ATTRIBUTES_EN_US = REGEXP_REPLACE(ATTRIBUTES_EN_US , '<attr name="[[:space:]]*([^[:space:]]{1,2}|[^[:space:]][^"]+[^[:space:]])[[:space:]]*">', '<attr name="\1">')
WHERE ATTRIBUTES_EN_US IS NOT NULL;

2.1.2 MS SQL

In MS SQL, the XML custom attributes are stored as an XML structure which cannot be updated via regular expressions. Instead the script needs to go through each XML node separately.

Cleanup XML custom attribute names MS SQL
SELECT 
    p.uuid,
	attr.value('(./@name)[1]', 'VARCHAR(MAX)') AS name,
	TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)')) AS trimmed_name
INTO #Temp
FROM table_name p
    CROSS APPLY column_name .nodes('/attrs/attr') t(attr)
	WHERE attr.value('(./@name)[1]', 'VARCHAR(MAX)') <> TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)'));

DECLARE @uuid AS NVARCHAR(28)
DECLARE @name AS VARCHAR(MAX)
DECLARE @trimmed_name AS VARCHAR(MAX)

WHILE (SELECT COUNT(*) FROM #Temp) > 0

BEGIN
	SELECT Top 1 @uuid = uuid, @name = name, @trimmed_name = trimmed_name FROM #Temp

	UPDATE table_name
	SET column_name.modify('replace value of (/attrs/attr[@name = sql:variable("@name")]/@name)[1] with sql:variable("@trimmed_name")')
	WHERE uuid = @uuid

	DELETE #Temp WHERE uuid = @uuid
END

DROP TABLE #Temp;

Example:

Cleanup XML custom attribute names MS SQL example
SELECT 
    p.uuid,
	attr.value('(./@name)[1]', 'VARCHAR(MAX)') AS name,
	TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)')) AS trimmed_name
INTO #Temp
FROM PRODUCT p
    CROSS APPLY attributes_en_us.nodes('/attrs/attr') t(attr)
	WHERE attr.value('(./@name)[1]', 'VARCHAR(MAX)') <> TRIM(attr.value('(./@name)[1]', 'VARCHAR(MAX)'));

DECLARE @uuid AS NVARCHAR(28)
DECLARE @name AS VARCHAR(MAX)
DECLARE @trimmed_name AS VARCHAR(MAX)

WHILE (SELECT COUNT(*) FROM #Temp) > 0

BEGIN
	SELECT Top 1 @uuid = uuid, @name = name, @trimmed_name = trimmed_name FROM #Temp

	UPDATE PRODUCT
	SET attributes_en_us.modify('replace value of (/attrs/attr[@name = sql:variable("@name")]/@name)[1] with sql:variable("@trimmed_name")')
	WHERE uuid = @uuid

	DELETE #Temp WHERE uuid = @uuid
END

DROP TABLE #Temp;

2.2 AV Custom Attributes

To migrate custom attributes that are stored in a separate AV database table, the following script needs to be executed, which works for both database systems.

The following placeholder needs to be replaced:

  • table_name → the affected database table, e.g. CATALOGCATEGORY_AV


Cleanup AV custom attribute names
UPDATE table_name SET name = TRIM(name)
WHERE name like ' %'
OR name like '% ';

Example:

Cleanup AV custom attribute names example
UPDATE CATALOGCATEGORY_AV SET name = TRIM(name)
WHERE name like ' %'
OR name like '% ';

Disclaimer

The information provided in the Knowledge Base may not be applicable to all systems and situations. Intershop Communications will not be liable to any party for any direct or indirect damages resulting from the use of the Customer Support section of the Intershop Corporate Web site, including, without limitation, any lost profits, business interruption, loss of programs or other data on your information handling system.

Customer Support
Knowledge Base
Product Resources
Tickets