Support for SQL Server XML typed columns and queries?

Hi,

Does LightSpeed support querying/updating/deleting of attributes and entities inside a SQL Server "xml" column type?  I have a need to store custom fields in our application and would like to use the powerful XML features of SQL Server 2005/2008 to do so, but will I have access to the SQL Server XML features from LightSpeed?

Specifically, executing queries similar to the ones found here:

http://www.sqlservercentral.com/articles/Miscellaneous/2996/

http://beyondrelational.com/blogs/jacob/archive/2008/07/24/xquery-lab-8-how-to-update-the-attribute-value-of-an-xml-variable.aspx

Is there some sort of "passthrough" mechanism I can use in LightSpeed to send down database specific queries like this?

This is not natively supported, but you could achieve it using stored procedures.  There is also a passthrough mechanism for loading entities (IUnitOfWork.FindBySql), though this is not currently available for updates, inserts or deletes.

Can I call a stored procedure that will still load a strongly-typed LS entity?

Also, can LS do automatic XML serialization to/from a SQL Server "xml" typed column?  For example, if I have a C# class that represents the data to be serialized into XML and stored in the SQL Server "xml" typed column, can LS serialize and deserialize that for me automatically?

Yes, a stored procedure can load a strongly-typed LightSpeed entity, provided the stored procedure results include an identity field.  See the IUnitOfWork.Find(ProcedureQuery) overload, or the SelectProcedure object in the designer (which generates a friendly wrapper over Find(ProcedureQuery)).  Also see Help Topics > LightSpeed > Stored Procedures in the docs.

No, LightSpeed doesn't do automatic XML serialisation and deserialisation.  Use a string field to contain the XML column data, and in the partial class create CLR wrapper properties that do that XML serialisation and deserialisation, e.g.

private string _address;

public Address Address {
  get { return Address.Deserialise(_address); }
  set { Set(ref _address, Address.Serialise(value), "Address"); }
}

Thanks for your quick replies!

Could I adopt the technique you mentioned using a wrapper property to do serialization/deserialization but KEEP the SQL Server column type as "XML" ?

The reason I want to do this is I'm probably going to want to use some of the native SQL Server XML query/update functions (which I can do via stored procedure as you mentioned).

So I guess in the model designer the field would be "string" but I'd want the underlying database column to be "xml".  Is that possible?

I believe so.  You will not be able to get the designer (or the migrations tool) to create the column as type XML, but if you create it using SQL Server Management Studio and drag it into the designer (or create it as string using the designer and hand-modify it to XML in SQL Server Management Studio) then the designer should not try to change anything when you do updates.

(You might be able to get it to create XML columns using a user-defined type, but it is probably not worth the effort.)

Note that by default the designer will create both a private field and a public property of type string.  If you want to suppress the public property so you can create your own strongly-typed wrapper property, change the property's Generation option to FieldOnly: you can then write your wrapper property in a partial class.

ok, thanks for the great replies :)