I have the impression that subscription data is located in the aspnet_profile table inside the PropertyValuesString, hence I include this table inside my sql string

SELECT aspnet_Users.UserName, aspnet_Roles.RoleName, aspnet_Profile.PropertyValuesString 
FROM aspnet_Users

JOIN aspnet_Profile.UserId
ON aspnet_profile.UserId = aspnet_Users.UserId

JOIN aspnet_UsersInRoles
ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId

JOIN aspnet_Roles
ON aspnet_Roles.RoleId = aspnet_UsersInRoles.RoleId

WHERE 
aspnet_UsersInRoles.RoleId = '70b760b7-100e-4993-b3fa-919116273294'

However the value of the PropertyValuesString is like this

<?xml version="1.0" encoding="utf-16"?>
<GuiSettings xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <ShowLanguageMissing>true</ShowLanguageMissing>
  <ShowLanguageFallback>true</ShowLanguageFallback>
  <ShowNew>true</ShowNew>
  <ShowNotPublished>true</ShowNotPublished>
  <ShowNotStarted>true</ShowNotStarted>
  <ShowExpired>true</ShowExpired>
  <ShowReadOnly>true</ShowReadOnly>
  <ShowNotVisibleInMenu>true</ShowNotVisibleInMenu>
  <ShowPageProviderIcon>true</ShowPageProviderIcon>
  <ShowShortcutIcon>true</ShowShortcutIcon>
  <ShowExternalIcon>true</ShowExternalIcon>
  <ShowInactiveIcon>true</ShowInactiveIcon>
  <ShowFetchDataIcon>true</ShowFetchDataIcon>
  <ShowEditingInProgressIcon>true</ShowEditingInProgressIcon>
  <ShowContainerPageIcon>true</ShowContainerPageIcon>
  <SettingsEnabled>false</SettingsEnabled>
</GuiSettings><?xml version="1.0" encoding="utf-16"?>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <string>/Global/Ikoner</string>
  <string>/Global/Ikoner/billetautomater</string>
  <string>/Global/Billeder/Epistore/Produkt - billeder</string>
</ArrayOfString>katrine@dsb.dk<?xml version="1.0" encoding="utf-16"?>
<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages />
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

I cannot see where in this xml subscription values should be located. There is ofcourse <SubscribedPages />, but this is empty, further more if not then how to retrieve the values in this element using sql.

Hi,

What kind of subscriptions are we talking about? Episerver Mail?

Yes it is mail subscriptions. 

Its when you want to subscribe to a specific kind of news

I'm not that familiar with Episerver Mail, but I'm pretty sure subscriptions are stored in Mail specific tables. Not in the asp.net profile data.

There is not such thing like mail specific tables. Besides this the xml output also show that there is a section for subscription data

<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages />
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

It only makes sense that subscription related info must be kept within above section. 

I would recommend downloading the SDK from here http://world.episerver.com/documentation/Items/SDKchm/SDK-documentation-download---EPiServer-75-Mail/ and see if you can find out where the subscribers are stored.
I'm pretty sure they are not stored in the regular asp.net user and profile tables, but rahter in tables with "common" in the name.

Otherwise you're not using Episerver Mail.

Do you have the Mail section in your global menu http://world.episerver.com/add-ons/episerver-mail/?

Hi Johan 

We do not have that (episerver-mail), neither do we have tables with "common" in their name. 

We are using EPiServer v6.

Ok, then I guess you are using the standard EPiServer Subscription functionality. Then you will either find it in tables in the

<SubscriptionInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <SubscribedPages /> <!-- This node till have your information... -->
  <LastMessage>2011-02-02T01:47:47.5160506+01:00</LastMessage>
  <Interval>0</Interval>
</SubscriptionInfo>

like you have above. Or use the API if you don't like to parse xml...(if you got more than 1000 users you need to modify the below allow paging of the profiles...)

int totalRecords = 0;
var allProfiles = ProfileManager.GetAllProfiles(ProfileAuthenticationOption.All, 0, 1000, out totalRecords);

...loop over all profiles and get their subscriptions by
 
foreach (SubscriptionDescriptor subscribedPage in profile.SubscriptionInfo.SubscribedPages)
{
var page = DataFactory.Instance.GetPage(new PageReference(subscribedPage.PageID));
}

where profile is your EPiServer profile...

Hi Daniel

Thanks for your suggestion, I'm trying to go that way, since it seem the only possible solution. However what I dont get the Episerver profile. Is this supposed to be in the web.config. This is what I could find there. 

 <profile enabled="true" defaultProvider="SqlProfile" automaticSaveEnabled="true">

How / where should I get the episerver profile?

That's the configuration for how the profile is stored yes. Do you have access to the source code? Or at least the site where it is running?
If I had been in your seat I would probably have done a custom scheduled job for EPiServer that exports that profile information about subscriptions. For that you don't even need the actual source code of the website and it's reusable if you need to do it again. You do need some coding skills in EPiServer and .NET though

Hi Daniel

You code regarding getting the profiles, did the trick. 

var allProfiles = ProfileManager.GetAllProfiles(ProfileAuthenticationOption.All, 0, 1000, out totalRecords);
 
  
foreach (SubscriptionDescriptor subscribedPage in profile.SubscriptionInfo.SubscribedPages)

 Thanks for our help

Sweet!