Net Tiers 2.0.1 + SQL Server 2005 + XML data type

I have a SQL Server 2005 database with a few database tables that have columns that use the XML data type. It seems the Find stored procedure Net Tiers generates does not conform to the SQL Server requirements.

I keep getting an error message whilst trying to create the procedure - "The xml data type cannot be compared or sorted, except when using the IS NULL operation). I have noted that in the Find procedure, it does a equality comparision as well as a IS NULL comparison. By removing the equality comparison, the procedure executes successfully.

I'm wondering if anyone is experiencing this issue and if there is a fix. I'm going to manually modify the procedure file for the interim.

Cheers,

Kent.

The problem, if I recall correctly, is that the XML type isn't really handled yet.  It's converted to a string internally and is treated the same by the stored procedure (as you found).  The best answer would be to update NetTiers to use an XML Document object in the code.  Ideally, it should use the XSD from the SQL Server to do client-side validation of the XML.

I only thought about this because our next software release (late '07) will make use of a lot of XML columns. 

Tony

That's a bit of a shame. I'll probably just check out how it is currently handling CLOBs and BLOBs and go from there. Because I'm pretty sure they are handled correctly in the stored procedure level.

At the moment I've just removed the comparison in the Find procedure which won't be a problem as you'd want to use XQuery on the XML column.

I'm not using a schema bounded XML column so validation is all handled on the code side which isn't too much of a problem.

Cheers,

Kent.