The ids created by the XML Source in Dataflow aren't guaranteed to be unique between calls for different xml files. How could they be?

Add a fileId column...

What method are you using to import the xml (within SSIS)? How are you calculating and assigning these IDs?

It's not entirely clear why you expect to get the 3 and 4 values twice, but you could create something like that using UNION ALL eg (using Chris' sample data)

SELECT...

I like XML FOR EXPLICIT, it's really powerful and allows that finer grain of control when constructing XML. However from SQL 2005 onwards, the other FOR XML options (...

There is a well-known issue with the xml datatype and methods and parent axis.

Try this instead:

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

GO

SELECT CAST( ConsolidatedPlayersCombinedText AS XML ) ConsolidatedPlayersCombinedText

INTO...

I did trial that and honestly there is not a lot in it, with solution 1 (mine) slightly faster 5-10% on average at scale of 1 million. My test...

That's a shame about the WHILE loop. I would encourage you to use one of the set-based options presented for you as they will scale much better.

How many claims...

Having taken all your comments into consideration, and with a similar approach to Chris, this option was best for me at scale ( 1 million ):

USE tempdb

GO

IF OBJECT_ID('#claims') IS NOT...

Nice and neat, but will roll up maxSequence of 0 to 20. Not to say that the OP has any of those, maybe they do; maybe they don't :...

Here's an example of how to do something like that using Common Table Expressions ( CTEs ):

USE tempdb

GO

IF OBJECT_ID('#claims') IS NOT NULL DROP TABLE #claims

CREATE TABLE #claims

(

claimNumberINT,

maxSequenceINT,

PRIMARY KEY ( claimNumber,...

Good spot Lynne, that was just in the demo so I could check the results were consistent between the three queries. Sorting is not a requirement from the OP...

I'm speculating coverageCodes could be a foreign key (as the name would suggest), but of course we don't have...

I would approach that using TOP and CROSS APPLY, eg:

SELECT c2.*

FROM ( SELECT DISTINCT CoverageCode FROM dbo.[Contract] ) x

CROSS APPLY ( SELECT TOP 5 * FROM dbo.[Contract] c WHERE x.CoverageCode...

I would approach that more like this:

DECLARE @xml XML

SET @xml = (

SELECT

(

SELECT

'ZID' AS "@_Name",

'789' AS "@_Value",

'ZID' AS "@_ID"

FOR XML PATH('KEY'), TYPE

),

(

SELECT

ZLN,

ZTLA,

ZMR,

ZCLS,

ZPITIP

FROM #ZDL ZL

FOR XML AUTO, ELEMENTS, TYPE

),

(

SELECT

ZBN,

ZFN,

ISNULL(ZMN,'') AS...

In-Memory Technologies in SQL Server 2014: CCI & XTP

Should be intense : )