I have a SQL query that is returning a huge number of users (175,000) and I want to filter the results a bit more.

One of the ways of doing this would be to check the value of an item held in a users profile.profiledata field.

The field is called ProfileEmailAlert and looks like this

<item key="6882:ProfileEmailAlert" type="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"><boolean>false</boolean></item>

I want to be able to access this via SQL, but have two issues

1) the data, although being XML, is stored in an NTEXT field.

2) The key is ModuleID:ProfileEmailAlert

So I would need a SQL query that would convert the NText to XML, then search for the item using the moduleID and name and then get the value (false in the example above).

Can anyone help me with the necessary SQL?

We have custom modules with user personalisation. We have stored various module settings in the profiledata field within the profile table.

We use this code to extract the setting:

Dim objPersonalizationController As New PersonalizationController
objPersonalization = objPersonalizationController.LoadProfile(UserID, PortalID)
strDefault = GetProfile(objPersonalization, ModuleID.ToString, strSettingName)


and this code to update the setting:

SetProfile(PI, ModuleID.ToString, SettingName, SettingValue)

I now want to access these settings in SQL

No, they are definitely in the profile table, held in the ProfileData field - I can see them clear enough, I just don;t know how to extract the values using SQL as the data type is NTEXT and the actual setting is XML.