Servicestack ORMLite - Using XML fields in PostgreSQL

Question

I have a web application that is being expanded to include PostgreSQL as a database option. For the existing MSSQL implementation, we use an XML column to save an ad-hoc object as part of our POCO class.

public class POCO
{
    [PrimaryKey]
    public int POCOId { get; set; }

    public string StringValue { get; set; }

    public string XMLValue{ get; set; }
}

In MSSQL we can insert a value as below, where GenerateXML generates valid xml that is converted to a string:

var poco= new POCO
        {
            StringValue ="My string here!"
            XMLValue= GenerateXML().ToString()
        };

        using (var context= new OrmContext())
            context.Connection.Insert(poco);

MSSQL accepts a string of XML and correctly inserts it into the database. However postgres requires xml to explicitly be inserted as xml:

42804: column "xmlvalue" is of type xml but expression is of type text

So instead of using a string in the poco model and inserting as a string, I need to cast that string to the xml type.

Is there any way to override the standard insert statement for postgres by flagging a field with an attribute, or otherwise marking it as xml?

My other option is to simply change the column to text, however that does making querying the column near impossible.

Answer 1

You can customize how different .NET Types are handled using OrmLite Type Converters however you wouldn't be able to customize string since that would affect all string fields.

Instead I've just added support for Xml in PostgreSQL in this commit by using a wrapper XmlValue Type that just wraps an XML string:

public struct XmlValue
{
    public string Xml { get; }
    public XmlValue(string xml) => Xml = xml;
    public override string ToString() => Xml;

    public bool Equals(XmlValue other) => Xml == other.Xml;

    public override bool Equals(object obj) => obj is XmlValue other && Equals(other);

    public override int GetHashCode() => Xml != null ? Xml.GetHashCode() : 0;

    public static implicit operator XmlValue(string expandedName) => 
        expandedName != null ? new XmlValue(expandedName) : null;
}

So we can customize how it's handled with a PostgreSqlXmlConverter:

public class PostgreSqlXmlConverter : PostgreSqlStringConverter
{
    public override string ColumnDefinition => "XML";
    public override void InitDbParam(IDbDataParameter p, Type fieldType) => p.DbType = DbType.Xml;
    public override object ToDbValue(Type fieldType, object value) => value?.ToString();
    public override string ToQuotedString(Type fieldType, object value) => 
        base.ToQuotedString(fieldType, value.ToString());
}

That you can register in your PostgreSqlDialect.Provider with:

PostgreSqlDialect.Provider.RegisterConverter<XmlValue>(new PostgreSqlXmlConverter());

Which you'll now be able to create tables and insert rows using the XmlValue data type, e.g:

public class XmlTest
{
    public int Id { get; set; }
    public XmlValue Xml { get; set; } 
}

Which you can create, insert and query using PostgreSQL XML functions:

using (var db = dbFactory.OpenDbConnection())
{
    db.DropAndCreateTable<XmlTest>();

    db.Insert(new XmlTest { Id = 1, Xml = @"<attendee>
<bio>
    <name>John Doe</name>
    <birthYear>1986</birthYear>
</bio>
<languages>
    <lang level=""5"">php</lang>
    <lang level=""4"">python</lang>
    <lang level=""2"">java</lang>
</languages>
</attendee>" });

        db.Insert(new XmlTest { Id = 2, Xml = @"<attendee>
<bio>
    <name>Tom Smith</name>
    <birthYear>1978</birthYear>
</bio>
<languages>
    <lang level=""5"">python</lang>
    <lang level=""3"">java</lang>
    <lang level=""1"">ruby</lang>
</languages>
</attendee>" });

    var results = db.Column<string>(@"SELECT
        (xpath('//bio/name/text()', Xml)::text[])[1]
        FROM xml_test 
        WHERE cast(xpath('//bio[birthYear>1980]', Xml) as text[]) != '{}'");
    results[0] //= John Doe
}

Please note your PostgreSQL installation will need to be configured --with-libxml to use the XML data type.

This change with XmlValue support is available from v5.7.1+ that's now available on MyGet.