Saving a xml data type to MS SQL server using grails domain class

Question

I'm trying to take advantage of MS SQL Server's xml data type. With grails version: 2.3.4 I have been using jdbc:h2 with the clob data type. When I switched databases to MS SQL Server 2012 everything works fine execpt when I try to save using the sqlType: xml and a UTF-8 encoding. UTF-16 encoding works fine... (but I need UTF-8 to work) Here is my code Domain class

class Message implements Serializable{

    long id 
    String message
    String messageType  

    static hasMany = [messageTracker: MessageTracker]

    static constraints = {      
        id nullable: false
        message (nullable: false, maxSize: 2147483646)
        messageType (inList: ["CDM", "MISMO"])      
    }

    static mapping = {
        table 'message'
        version false
        id column: 'message_id', generator: 'identity'
        message column: 'message', type:'text', sqlType: 'xml'
        messageType column: 'message_type'
    }
}

Service:

private Message insertMessage( String message, String messageType){
        log.info("Inside insertMessage Service")
            Message msg = new Message(message: message, messageType: messageType.toUpperCase())           
        if(!msg.save()){
                   log.error "Trouble saving message payload to Message table" + msg.errors
           throw new ValidationException(msg.errors)
        }

        return msg
    }

The strange thing that happens is if I try to save

<?xml version="1.0"  encoding="UTF-16"?>
<tag> this is a blah</tag>

everything works fine. (The reason I can't use encoding utf-16 is because it is out of my control, the xml is going to be in utf-8) But!

if I try and save this

<?xml version="1.0"  encoding="UTF-8"?>
<tag> this is a blah</tag>

It crashes and burns with this error:

XML parsing: line 1, character 39, unable to switch the encoding. Stacktrace follows:
com.microsoft.sqlserver.jdbc.SQLServerException: XML parsing: line 1, character 39, unable to switch the encoding
        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
        at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)
        at secMgr.DataService.insertMessage(DataService.groovy:79)
        at secMgr.DataService$_$tt__createTransaction_closure4.doCall(DataService.groovy:142)
        at org.grails.datastore.gorm.GormStaticApi.withTransaction(GormStaticApi.groovy:698)
        at secMgr.DataService.$tt__createTransaction(DataService.groovy:140)

The thing that really confuses me is that when I try to insert UTF-16 encoded xml directly into the table through Microsoft SQL Server Management Studio I get the same error:

Msg 9402, Level 16, State 1, Line 1
XML parsing: line 1, character 40, unable to switch the encoding

While UTF-8 encoding works fine with a direct insert.

I feel like I'm missing something important here...

PJT PJT · Accepted Answer · 2014-06-10T14:13:11

Changing String to byte array before saving to database resolved the issue...sounds trivial but took a lot of trial and error. Here is the fix in my examples: My Domain class: class Message implements Serializable{

    long id 
    byte[] message
    String messageType  

    static hasMany = [messageTracker: MessageTracker]

    static constraints = {      
        id nullable: false
        message (nullable: false, maxSize: 2147483646)
        messageType (inList: ["CDM", "MISMO"])      
    }

    static mapping = {
        table 'message'
        version false
        id column: 'message_id', generator: 'identity'
        message column: 'message'
        messageType column: 'message_type'
    }
}

My Service

private Message insertMessage( String message, String messageType){
        log.info("Inside insertMessage Service")

        Message msg = new Message(message: message.getBytes(), 
                                    messageType: messageType.toUpperCase())

        if(!msg.save()){
            log.error "Trouble saving message" + msg.errors
            throw new ValidationException(msg.errors)
        }

        return msg
    }

One note, I did find a "bad" solution where I just removed the xml tag which included version and encoding from the message string, where I created a holder string and then used this regular expressioN:

message = messageOld.replaceAll(~/<\?xml(.+?)\?>/", "").trim();

Came up with this bad solution based on this answer how to remove Xml version string from String

Saving a xml data type to MS SQL server using grails domain class

1 Answers