Hi,

I just love your posts and tutorials. Thanks for contributing. I have the following requirement and wanted to know if the solution is optimal performance wise -

I have a NTEXT column in a table which stores a serialized C++ / COM object, like a Property Bag - Key/Value pair (legacy design, can't help much). Now, this object can grow in size exponentially because it is configurable externally. The number of rows in this Table are 5,00,000 + in live scenario. I have to query this column for reporting purpose.

Solution (i am thinking) - Convert the COM / Object into XML and store as a separate column and then query this column and use in Dynamic SQL for report generation purpose?

Now, this XML schema can be different or same for each unique rows. The only way to uniquely identify is that the element under ROOT will have a type attribute like -

<ROOT xmlns:...>
  <Module type='Customer'/>
  <Country Name='India' State='Kerala' District=xsi:nil/>
...
</ROOT>

So, the user might now want to query , get me all customers whose District is NULL. So, like this there can be multiple columns and all columns should satisfactorily be part of the filter.

<ROOT xmlns:...>
  <Module type='Seller'/>
  <Product Name='Toy' Brand='Funskool'/>
...
</ROOT>

So, the user might now want to query , get me all sellers who sell Funskool toys. So, like this there can be multiple columns and all columns should satisfactorily be part of the filter.

Your advice is highly appreciated.

Submitted under:  · Microsoft SQL Server ·  ·  ·