Provide a way to define primary and secondary XML Indexes

[ashishs]

Exposed currently has no way to define xml indexes in MS SQL Server.

E.g:

CREATE PRIMARY XML INDEX [primary_xml_index_name] ON [table_name]([xml_column_1])

CREATE XML INDEX secondary_xml_index_name ON [table_name]([xml_column_1]) USING XML INDEX [primary_xml_index_name] FOR VALUE

Secondary xml indexes can be on PATH, VALUE, or PROPERTY Documentation at XML Indexes

Note: a probable fix is to allow a listOf < DdlAware > objects to be added ad-hoc to a Table and have run after the table is created. The Index class is defined as a data class which prohibits subclassing.

[AlexeySoshin]

Hi @ashishs This seems a very DB-specific feature to me. My suggestion would be to implement this using a prepared statement instead.

[ashishs]

@AlexeySoshin Even if this is created using a prepared statement, ther create missing columns method of the SchemaUtils breaks this. This happens in the code where indexes are checked. So creating the schema from code is not possible if XML indexes are used.

[bog-walk]

@ashishs Regarding primary xml indexes, as a start, could you please confirm if creating it by leveraging indexType works?

val tester = object : Table("tester") {
    val id = integer("id")
    val primaryXml = xml("primary_xml_column") // assumes a custom column type
    
    override val primaryKey = PrimaryKey(id)
    
    init {
        index("primary_xml_index", false, primaryXml, indexType = "PRIMARY XML")
    }
}
// generates SQL
CREATE TABLE tester (id INT PRIMARY KEY, primary_xml_column XML NOT NULL)
CREATE PRIMARY XML INDEX primary_xml_index ON tester (primary_xml_column)

If this doesn't work for you, please consider providing the exception you're getting, as well as how the xml column type is declared and registered for use, so we can take a further look.