DB22SS0573: XML index cannot be converted (Error)

This article describes the reason why SQL Server Migration Assistant (SSMA) for Db2 can't 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 can use primary/secondary XML indexes in SQL Server for improving query performance, there's 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.

Examples

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 can't 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 in the following example:

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.