October 24, 2017 at 5:56 am
Guys,
I am trying to redesign a terrible process for generating financial statements with a funky SQL based XML solution. the XML document structure is as follows (closing tags omitted for brevity).
<Statements>
<Statement>
<SpecialInstructions>
<SpecialInstruction>
<StatementMessages>
<StatementMessage>
<AccountID>
<Address>
<AddressLine1>
<AddressLine2>
<AddressLine3>
<PostCode>
<Account>
<AccountMessages>
<AccountMessage>
<SubAccount>
<Balances>
<OpeningBalance>
<ClosingBalance>
<Transactions>
<Transaction>
<Ref>
<Description>
<Date>
<Debit>
<Credit>
<BalAfterTxn>
The File will contain a number of statements for different customers. Within each Statement there can be one or more accounts. Each account will have it's own opening and closing balances. Each account may have 0..n transactions. Messages may be applied at customer (Statment) level or account level depending on the message type. Special Instructions are for the printer to do conditional formatting (e.g. large print)
The query is using nested SELECT...FOR XML PATH ('InnerTag'), ROOT ('OuterTag') and the query performance is pretty good. (17 seconds to process statements for 96K sub-accounts) for 3 months
HOWEVER. I am really struggling to output the results in a format that is readable by the printers. Smaller copies of the results set (5000 acccounts) view perfectly in SSMS when output is grid. Much more than that and I get an out of memory error. But exporting to a file simply results in the text being truncated as 256 characters per line even with the Tolls->options->Query Results -> SQL Server -> Results to text -> Maximum number of charages displayed in each column = 8192.
The query says that it has output 23522 rows although there are 64605 lines in the output file so I am not sure what SSMS is classifying as a row - I presume it is 1 row per account as this is the primary driver for the logic and roughly equates to the number of active accounts we have but I can't see any logic about how it is breaking the lines in the output file. The first line truncates half way through the </AddressLine1> closing tag but the 2nd line starts part way through the <Description> text (i.e. has dropped the rest of the address fields and closing address tag, All the Account, Subaccount and balance details, the transactions group tag and possibly some of the transaction tags as well.
What is the solution to be able to export this XML file as a valid XML format. The whole file is 131,341,235 characters long (for 3 months data) and 208,337,199 characters for 28 weeks
October 24, 2017 at 10:12 am
Quick thought, if you need to do this in SSMS then split the XML on the Statement elements with the nodes function which puts each of those in a separate row, otherwise export the result set straight into a txt file and take it from there.
😎
Using Results to text in SSMS is far too limited for anything like this.
October 25, 2017 at 8:22 am
Thanks for the input Eirikur. Could you post an example of what you mean. I need the data to come out as a single file of N Statements.
I have managed to generate the file by using SSIS following the detaisl in the link below but the performance is woeful. Not a problem for me because we only need to run this once a quarter and can do so overnight but I hate slow code :). The stored procedure runs in about 35 seconds for 3 months of data, the same sproc called from SSIS takes forever. Left it running for over 2 hours and it still had not completed
http://www.sql-server-performance.com/2013/export-to-xml-using-ssis/
October 25, 2017 at 8:24 am
UPDATE:
Fixed the issue with the SSIS package. Make sure you use an ADO.NET connection and not an OLEDB. Normally OLEDB is expected to be faster, but not in this case. This may be due to the size of the file (76MB) that I am creating.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply