Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello,
I need your help. We must create an xml file with invoices informations for goverment e-books. Then we must upload it and take back a response.
For now I create xml file from a select query:
SELECT invoiceNr, invoiceDate FROM invoiceHeader 
        OUTER APPLY
        (
            SELECT  
			lineNumber, netValue, vatCategory
			FROM invoiceDetails 
			WHERE invoiceDetails.headid = invoiceHeader.unid FOR XML PATH('invoiceDetails')
        ) XmlinvoiceDetails(invoiceDetails)
FRO XML PATH('invoice'), root('invoicedoc')
and using SQLDataReader and StreamWriter we are saving xml on disk.

Then with WebRequest we upload xml file and get a response back as xml file. That file (response.xml) has a sequential number 1,2,3 e.t.c. with information (success or not) for every invoice node we upload.

First of all I need to know if there is another way of creating xml file, more efficient because in xml file we will have many invoices and we want very quickly to create xml file.

Second, we want to find a way of updating records we have exported in xml file, perhaps with unique index because we want to know every record in which xml file has been exported and with which order or in which xml line node has been written, in order getting the response.xml with the results to update each record with the status code (success or not).

Pls help us, we need your opinion and of course we need to know the most efficient way to do this because there are millions of records.

What I have tried:

above I explain what I have tried
Posted
Updated 27-Aug-20 17:44pm
v2
Comments
Garth J Lancaster 28-Aug-20 4:17am    
It might be handy to provide more information on the 'size' of "there are millions of records." - use Improve question

At a quick guess, a lot of time could be wasted writing to the file on disk - I would be looking at
1) making the acquire xml 'data' (I'm not going to use 'file' as a term here) async
2) hold the data in maybe an XMLDocument in-memory

This may mean getting smaller sets of XML ie 'batching' rather than one big blob, if it is at all possible, but that's the principle, reduce the 'file' IO, make it async
gg2007mva 28-Aug-20 16:35pm    
Thank you very much for your response.
I need a little more help. I have created classes for XML schema and I want to know if there is a way to load immediately SQL query into those classes in once and not loop through all rows to built XML.
I need to know if there is a way to link dataset with XML classes, in which every datatable of the dataset is (typeof) every XML class.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900