Need experts Advice

Question

Need experts Advice

JoNTSQLSrv

Default port

Points: 1420
More actions
February 14, 2018 at 10:13 am

#344044

HI guys,
I need expects advice to do below things with great performance.
A Table has 10 million ID's records. My task is to generate .xml file so that 1 xml file should has 1000 ID information. I have done xml out for 1 ID using for xml clause
1. I want advice on how to generate it as .XML file with specific name - name to be pick from table .
2. I want to know how to split whole 10 million ID to 10,000 XML file with each xml having 1000 ID information in it.
Please give me idea , how to do it ?
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 1

You can use NTILE to split rows between batches, more details here - https://docs.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql

JoNTSQLSrv Default port Points: 1420 More actions · Answer 2

Evgeny Garaev - Wednesday, February 14, 2018 6:37 PM
You can use NTILE to split rows between batches, more details here - https://docs.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql

Thanks Evgeny Garaev,
I will try NTILE and check how it is getting spited .

Do you have any idea how to generate xml file from these xml output ? is it good to do via sp or via ssis ?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Evgeny Garaev - Wednesday, February 14, 2018 6:37 PM
You can use NTILE to split rows between batches, more details here - https://docs.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql

NTILE will control the number of groups. It will not, unless you get very lucky with even numbers, allow you to control and limit the actual batch size to 1000 or any other number.

What is needed is a ROW_NUMBER()-1 (to provide a sequential number starting at zero) and an integer division divide by 1000 of that row number to create group numbers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

JoNTSQLSrv - Wednesday, February 14, 2018 10:13 AM
HI guys,
I need expects advice to do below things with great performance.
A Table has 10 million ID's records. My task is to generate .xml file so that 1 xml file should has 1000 ID information. I have done xml out for 1 ID using for xml clause
1. I want advice on how to generate it as .XML file with specific name - name to be pick from table .
2. I want to know how to split whole 10 million ID to 10,000 XML file with each xml having 1000 ID information in it.
Please give me idea , how to do it ?
Thanks in advance

Do the people that made that request actually understand that 1) it will expand the amount of space required by somewhere between 8 and 16 times and 2) that 10 million divided by 1000 means that there will be 10,000 files filled with XML junk and 3) there are much more effective methods for transferring such large numbers of rows?

With that, I have to ask, what is the business reason behind this request? What is the ultimate goal and what kind of system will receive the data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 5

JoNTSQLSrv - Wednesday, February 14, 2018 10:13 AM
HI guys,
I need expects advice to do below things with great performance.
A Table has 10 million ID's records. My task is to generate .xml file so that 1 xml file should has 1000 ID information. I have done xml out for 1 ID using for xml clause
1. I want advice on how to generate it as .XML file with specific name - name to be pick from table .
2. I want to know how to split whole 10 million ID to 10,000 XML file with each xml having 1000 ID information in it.
Please give me idea , how to do it ?
Thanks in advance

Generate a seq no on ID column of the table and use SSIS conditional split to split the records in each batch. You could use C# to pass the

parameters in your code. Use recordset destination to pass the table value to 1 XML file each.

I haven't tried this requirement, Maybe if you could start from your end, We'll together try to accomplish this.