locked
Header multiple detail XML File RRS feed

  • Question

  • Hi can someone please help me, I have this Code,

                string connetionString = null;
                SqlConnection connection;
                SqlDataAdapter adapter ;
                DataSet ds = new DataSet();
                string sql = null;
    
                connetionString = "Data Source=10.88.58.21;Initial Catalog=WMPRDARC1;User ID=sa;Password=sql";
                connection = new SqlConnection(connetionString);
                sql = "SELECT RECEIPTKEY,SKU,QTYRECEIVED,UNITPRICE,EXTENDEDPRICE,'Hello' FROM wmwhse1.RECEIPTDETAIL WHERE RECEIPTKEY in ('0001995937','0001993690')";
                try
                {
                    connection.Open();
                    adapter = new SqlDataAdapter(sql, connection);
                    adapter.Fill(ds);
                    connection.Close();
                    ds.WriteXml("Product.xml");
                    MessageBox.Show("Done");
                }
    
    

    the result is this 

    <?xml version="1.0" standalone="yes"?>
    <NewDataSet>
      <Table>
        <RECEIPTKEY>0001995937</RECEIPTKEY>
        <SKU>199926</SKU>
        <QTYRECEIVED>5.00000</QTYRECEIVED>
        <UNITPRICE>31.81</UNITPRICE>
        <EXTENDEDPRICE>44</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>202374</SKU>
        <QTYRECEIVED>3.00000</QTYRECEIVED>
        <UNITPRICE>8.49</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>205818</SKU>
        <QTYRECEIVED>1.00000</QTYRECEIVED>
        <UNITPRICE>8.83</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>205955</SKU>
        <QTYRECEIVED>1.00000</QTYRECEIVED>
        <UNITPRICE>10.39</UNITPRICE>
        <EXTENDEDPRICE>17</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>206730</SKU>
        <QTYRECEIVED>2.00000</QTYRECEIVED>
        <UNITPRICE>8.54</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>226028</SKU>
        <QTYRECEIVED>5.00000</QTYRECEIVED>
        <UNITPRICE>9.08</UNITPRICE>
        <EXTENDEDPRICE>14</EXTENDEDPRICE>
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
        <SKU>226400</SKU>
        <QTYRECEIVED>2.00000</QTYRECEIVED>
        <UNITPRICE>10.29</UNITPRICE>
        <EXTENDEDPRICE>16</EXTENDEDPRICE>
      </Table>
    </NewDataSet>

    but <g class="gr_ gr_169 gr-alert gr_tiny gr_spell gr_inline_cards gr_run_anim ContextualSpelling multiReplace" data-gr-id="169" id="169">i</g> want result should be like this. 

    <?xml version="1.0" standalone="yes"?>
    <NewDataSet>
      <Table>
        <RECEIPTKEY>0001995937</RECEIPTKEY>
      <Detail>  
      <SKU>199926</SKU>
        <QTYRECEIVED>5.00000</QTYRECEIVED>
        <UNITPRICE>31.81</UNITPRICE>
        <EXTENDEDPRICE>44</EXTENDEDPRICE>
      </Detail>  
      </Table>
      <Table>
        <RECEIPTKEY>0001993690</RECEIPTKEY>
      <Detail>  
        <SKU>202374</SKU>
        <QTYRECEIVED>3.00000</QTYRECEIVED>
        <UNITPRICE>8.49</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Detail>  
      <Detail>  
        
        <SKU>205818</SKU>
        <QTYRECEIVED>1.00000</QTYRECEIVED>
        <UNITPRICE>8.83</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Detail>  
      <Detail>  
        
        <SKU>205955</SKU>
        <QTYRECEIVED>1.00000</QTYRECEIVED>
        <UNITPRICE>10.39</UNITPRICE>
        <EXTENDEDPRICE>17</EXTENDEDPRICE>
       </Detail>  
      <Detail>  

        <SKU>206730</SKU>
        <QTYRECEIVED>2.00000</QTYRECEIVED>
        <UNITPRICE>8.54</UNITPRICE>
        <EXTENDEDPRICE>13</EXTENDEDPRICE>
      </Detail>  
      <Detail>  
        
        <SKU>226028</SKU>
        <QTYRECEIVED>5.00000</QTYRECEIVED>
        <UNITPRICE>9.08</UNITPRICE>
        <EXTENDEDPRICE>14</EXTENDEDPRICE>
      </Detail>  
      <Detail>  
        
        <SKU>226400</SKU>
        <QTYRECEIVED>2.00000</QTYRECEIVED>
        <UNITPRICE>10.29</UNITPRICE>
        <EXTENDEDPRICE>16</EXTENDEDPRICE>
      </Detail>  
    </NewDataSet>


    • Moved by CoolDadTx Friday, May 25, 2018 2:27 PM ADO.NET related
    Friday, May 25, 2018 11:37 AM

Answers

  • You're relying on the default dataset schema generation to generate the XML. That is really only designed for allowing you to export data and then import it again elsewhere. It is not designed as a general, configurable XML format. For that kind of control you'll need to use XmlSerializer.

    There is a little room for configuration via the mapping properties but I don't think you're going to be able to nest data like you want. I think your best bet is to use a data reader to read the data into a .NET type that you create and then serialize that type out using XmlSerializer so you can properly annotate and structure the output the way you want.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Alucard023 Saturday, May 26, 2018 5:34 AM
    Friday, May 25, 2018 2:27 PM

All replies

  • You're relying on the default dataset schema generation to generate the XML. That is really only designed for allowing you to export data and then import it again elsewhere. It is not designed as a general, configurable XML format. For that kind of control you'll need to use XmlSerializer.

    There is a little room for configuration via the mapping properties but I don't think you're going to be able to nest data like you want. I think your best bet is to use a data reader to read the data into a .NET type that you create and then serialize that type out using XmlSerializer so you can properly annotate and structure the output the way you want.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by Alucard023 Saturday, May 26, 2018 5:34 AM
    Friday, May 25, 2018 2:27 PM
  • thanks i got an idea a much easier way i made a script on SQL :)
    Saturday, May 26, 2018 5:35 AM