Happy new year all 🙂

Giving the original OP the benefit of the doubt, it looks like either the load strategy has been changed or different sources are employing...

Glad we have all been infused with the festive spirit and made up 🙂

Lost count of the number of jobs I have 'lost' by being a 'smart-arse' on...

not a cursor to run the extract, a cursor to generate the SQL code for the nested XML

declare @tbl as table
(
    nodename nvarchar(10)
)

Thinking outside the box, could you write a cursor that prints out the SQL for the nested FOR XML and then copy-paste that into your logic

Code that writes...

If you are running queries against a remote database then I would suggest that you set up the tables/views in the remote database as Synonyms and reference these in your...

Thom,

Unless I am mis-reading your problem, the frame code is an attribute not a tag as all of the sub-tags have the same structure and your file should look...

Sorry Drew

What I meant was that using UNION ALL could result in duplicate rows which when extended through a join would then effectively create a cartesian results set....

Tracey,

I think you will need to post DDL and data for us to help you.Otherwise the short answer is "Because they don't match so are not distinct"

@ Jeff,

Is that midnight your time, my time or SQLServerCentral time :0

don't see why it would make a different but try swapping the UNION for UNION ALL

UNION will drop duplicated records but UNION ALL won't - which could result...

I think what the OP is asking is what is the precedence for the various clauses in the SQL statement and it is a valid question.

The example given...

materializing the CTE_NOW_NEXT seems to have done the trick.  5000 accounts now takes 2m14s including the materialization of the CTE.

FWIW I prefer to write these sorts of problems as Common Table Expressions. 

WITH CTE_WidgetRank AS
(
        SELECT
                    *,
                    ROW_NUMBER() OVER (PARTITION BY CAST(DateCreated AS...

Or do you have Excel as an option:

output the data as
StudentID, Subject, Test#, Score

Use this as a data set for a pivot table
Student ID goes into rows

Fells like a job for PIVOT to me.
You say that the number of tests is fixed at three, but not the number of subjects so there could be more...