Need experts Advice

  • 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

  • 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

  • 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 ?

  • 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)

  • 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)

  • 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.

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

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