locked
Create XML File RRS feed

  • Question

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


    Thursday, August 27, 2020 10:31 PM

All replies

  • Hi Mary1982,

    Thank you for posting here.

    Using bcp.exe to run stored procedures and import the results into an xml file may be a viable option.

    bcp.exe is usually downloaded along with the SQL Server.

    Creating an XML format file

    But I have not used a huge table for actual testing, so I am not sure whether this method will be faster than your current method, you can give it a try.

    If you want to modify an xml file, you can refer to the example in the link below.

    Write XML directly to disk and append elements

    Best Regards,

    Timon


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 28, 2020 7:31 AM
  • 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.
    Friday, August 28, 2020 8:35 PM
  • Typically users don't know how to deal with millions of records so if you go that route and need to display it usually that is a time consuming process to read and deal with that many records. So consider is this really what you want.

    In regards to loading data from SQL-Server look at DataSet.ReadXml.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, August 28, 2020 9:33 PM
  • Hi Mary,

    You mentioned that you've created classes for XML schema. Are these .xsd files? If so, you can create Typed DataSets from .xsd files pretty easily. Simply add an .xsd to your DataSet project, right-click on the .xsd and choose "Run Custom Tool". This is what will generate the Typed DataSet for you. If that option doesn't show up in your context menu, choose Properties instead and type "MSDataSetGenerator" in the Custom Tool property. After that, any time you make a change to the .xsd in the XML Editor and save the change, the Typed DataSet gets regenerated.

    Then you can use these Typed DataSets to hold your data. A DataSet (Typed or not) makes it pretty easy to write out an XML file (containing many rows of data).  Simply use this.MyDataSet.WriteXml("filename.to.write.to")

    You can use this.MyDataSet.Load(dr) --- where dr is your SqlDataReader -- to load the data into your DataSet.

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, August 29, 2020 1:02 AM
  • hi

    The query result stored in dataset and convert to xml

     ds.Tables[0].WriteXml("D://x.xml");

    // here the ds is the dataset .

    Thanks and regards

    Saturday, August 29, 2020 5:50 PM
  • Thanks you very much, I created custom dataset as you suggest me. 

    Pls I need your help and in something else, I need to save the data in master and details tables. And for this reason I want to get the autoincrement id of master table. 

    dCallerRow.SetParentRow(mCallerRow); 

    It does not work, I cannot see the id to dataset.

    Best regards

    Saturday, August 29, 2020 10:23 PM
  • What does your XSD look like? Just post the relevant parts of it (like the two related tables with only a few of the columns).  The MSDataSetGenerator should have automatically set that up for you in the .Designer.cs file.

    Use the "Insert Code Block"  to post it:


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Saturday, August 29, 2020 10:43 PM added code block photo
    Saturday, August 29, 2020 10:41 PM