Paging a "WITH XMLNAMESPACES - FOR XML" query

HI all, I would like to add paging to a query like this:

Code Snippet

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

)

SELECT Field1 as 'cnt:Field1'

, Field2 as 'cnt:Field2'

, Field3 as 'cnt:Field3'

FROM MyTable

FOR XML PATH ('MyTable');

I thought I should do something like:

Code Snippet

WITH T1 AS

(

SELECT Field1

, Field2

, Field3

, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNumber

FROM MyTable

)

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

)

SELECT T1.Field1 as 'cnt:Field1'

, T1.Field2 as 'cnt:Field2'

, T1.Field3 as 'cnt:Field3'

FROM T1

WHERE RowNumber between 50 and 60

FOR XML PATH ('MyTable');

But this doesn't work, seems there's some sintax problem with the 2 "WITH".

Does anyone knows if it's possible to obtain what I want? What is wrong with my code?

Thanks in advance

m.

Do:

Code Snippet

WITH XMLNAMESPACES

(

'http://www.w3.org/2001/XMLSchema' as xsd

, 'http://www.w3.org/2001/XMLSchema-instance' as xsi

, 'http://schemas.contoso.com/' as cnt

),

T1 AS

(

SELECT Field1

, Field2

, Field3

, ROW_NUMBER() OVER (ORDER BY Field1) AS RowNumber

FROM MyTable

)

SELECT T1.Field1 as 'cnt:Field1'

, T1.Field2 as 'cnt:Field2'

, T1.Field3 as 'cnt:Field3'

FROM T1

WHERE RowNumber between 50 and 60

FOR XML PATH ('MyTable');

|||

Perfect (and even so quick!).

Many thanks

m.