Rethinking XML Column Usage in Database Tables: A Practical Insight

Hello, fellow database professionals,

Unfortunately, there are still instances where XML columns are defined within database tables. I strongly advise against this practice for future designs. The reason is straightforward: XML columns consume a significant amount of storage space, and the query retrieval times can be remarkably poor.

However, I recently stumbled upon a surprisingly simple method for querying such data without the need for XPath. This involves converting the XML data to nvarchar. Surprisingly, this method not only simplifies the query process but also slightly improves the performance. It's not a perfect solution, but it's a step in the right direction.

Consider this example from a query on a substantially large table:

• Using XPath, the query execution time was nearly 7 minutes.
• By casting to nvarchar, the time reduced to approximately 5 minutes.

Here's the approach:

WHERE CAST(ColumnName AS NVARCHAR(MAX)) LIKE '%xxxx%'