Skip to content

db22ss0573.md

Latest commit

 

History

History
74 lines (55 loc) · 2.71 KB

db22ss0573.md

File metadata and controls

74 lines (55 loc) · 2.71 KB
title DB22SS0573: XML index cannot be converted (Error)
description Describes the reason why SQL Server Migration Assistant (SSMA) for DB2 cannot convert an index on an XML column.
authors nahk-ivanov
ms.service ssma
ms.devlang sql
ms.topic article
ms.date 1/22/2020
ms.author alexiva

DB22SS0573: XML index cannot be converted (Error)

This article describes the reason why SQL Server Migration Assistant (SSMA) for DB2 cannot convert an index on an XML column.

Background

In DB2 you can create an index on an XML column for efficient evaluation of XQuery expressions to improve performance for queries on XML documents. You can also define constraints (like uniqueness) based on attributes or values within the XML document.

In simple relational indexes, index keys are composed of one or more table columns that you specified. However, an XML index uses a particular XML pattern expression to index paths and values in XML documents that are stored in a single XML column.

While you may use primary/secondary XML indexes in SQL Server to improve query performance, there is no direct analogy in SQL Server to XML indexes that enforce constraints on the XML data. When SSMA encounters an index with XML pattern it generates an error message.

Example

Consider the following example, where INFO column contains XML data and we want to make sure all records in the CUSTOMERS table have unique CID value in that column:

CREATE TABLE SAMPLE.CUSTOMER
(
    CID BIGINT NOT NULL,
    INFO XML,
    HISTORY XML
);

CREATE UNIQUE INDEX SAMPLE.CUST_CID_XMLIDX ON SAMPLE.CUSTOMER
(
    INFO ASC
)
GENERATE KEY USING XMLPATTERN '/CUSTOMERINFO/@CID' AS SQL DOUBLE IGNORE INVALID VALUES;

When you try to convert the CUSTOMER table in SSMA, it generates the following error message:

DB22SS0573: XML index cannot be converted

Possible remedies

In SQL Server, to enforce uniqueness based on the value in XML document, you can use computed column with user-defined function, as shown below:

CREATE FUNCTION [ExtractCID](@info xml)
RETURNS INT
WITH SCHEMABINDING
AS BEGIN
    RETURN @info.value('(/CUSTOMERINFO/@CID)[1]', 'int')  
END
GO

CREATE TABLE [SAMPLE].[CUSTOMER]
(
    [CID] bigint NOT NULL,
    [INFO] xml NULL,
    [HISTORY] xml NULL,
    [INFO$CID] AS dbo.ExtractCID([INFO])
)
GO

CREATE UNIQUE INDEX [CUST_CID_XMLIDX] ON [SAMPLE].[CUSTOMER] ([INFO$CID])

Important

Adding an extra column to the table (computed column in this case) will affect all SELECT * queries. Make sure your application explicitly lists all columns in the SELECT statements or disregards any extra columns that might be returned.