Action Pants

DotNetNuke is a great portal and there are many modules out there to make working with it easier. One such module is BulkReg. It allows you to throw data into it in a csv or xml file and it adds, deletes, or updates those users in DNN.
I ask my clients for their member list in a xls file and then I import that information into a table in a SQL Server database. Now with it in a table I can use the FOR XML statement to build my xml for the module. I built a Stored Procedure with paging functionality because I have found that i'll run into time-out issues if I try to load all the members at the same time. The SP asks for the PageIndex and then the Size of each page.


CREATE PROCEDURE [dbo].[GetDNNMemberXML]
-- Add the parameters for the stored procedure here
@PageIndex int = 1,
@PageSize int = 500
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here

WITH TransformedMembers AS (
SELECT ROW_NUMBER() OVER (ORDER BY UserName ASC)
AS Row,
USERNAME,
FIRSTNAME,
LASTNAME,
UNIT,
STREET,
CITY,
REGION,
POSTALCODE,
TELEPHONE,
EMAIL,
FROM dbo.DNNMembers)

SELECT
'R' AS [KEY],
USERNAME,
USERNAME AS DISPLAYNAME,
FIRSTNAME,
LASTNAME,
UNIT,
STREET,
CITY,
REGION,
'US' AS COUNTRY,
POSTALCODE,
TELEPHONE,
EMAIL,
'UserRole' AS "ROLES/ROLE/ROLENAME"
FROM TransformedMembers WHERE Row BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex*@PageSize
FOR XML PATH('USER'), ROOT('ROOT')
END

GO


I used this MSDN article and this other article to figure out what statements to use for the FOR XML and this one for Paging. The functions used for paging in this SP are only available in SQL Server 2005. Hope somebody finds this useful.