Export sql data to xml

Question

i exporting data from sql to xml, but i have problem with format of xml. i have this output now:

<?xml version="1.0" standalone="yes"?>
    <ITEMS>
      <ITEM>
        <CATALOG_NUMBER>1047</CATALOG_NUMBER>
        <ITEM_NAME>Adrian3 Navy Grey Red </ITEM_NAME>
        <UNIT>KUS  </UNIT>
        <COUNTRY>CZ  </COUNTRY>
        <CURRENCY>CZK</CURRENCY>
        <PRICE_VAT>797.00</PRICE_VAT>
        <PRICE_VAT1>21.0</PRICE_VAT1>
        <NOTE>Sportovní bota v barvě...
        </NOTE>
      </ITEM>
      <ITEM>
        <CATALOG_NUMBER>106</CATALOG_NUMBER>
        <ITEM_NAME>Betty Hot pink</ITEM_NAME>
        <UNIT>KUS  </UNIT>
        <COUNTRY>CZ  </COUNTRY>
        <CURRENCY>CZK</CURRENCY>
        <PRICE_VAT>797.00</PRICE_VAT>
        <PRICE_VAT1>21.0</PRICE_VAT1>
        <NOTE>Krásná a jednoducháým...
        </NOTE>
      </ITEM>
      <ITEM_GROUPS>
        <GROUP>00010000000000000000</GROUP>
        <GROUP_NAME>Pediped</GROUP_NAME>
      </ITEM_GROUPS>
      <ITEM_GROUPS>
        <GROUP>00010001000000000000</GROUP>
        <GROUP_NAME>Original</GROUP_NAME>
      </ITEM_GROUPS>
      <ITEM_GROUPS>
        <GROUP>00010002000000000000</GROUP>
        <GROUP_NAME>Gripngo</GROUP_NAME>
      </ITEM_GROUPS>
    </ITEMS>

But i need this format:

<ITEMS>
  <ITEM>
    <CATALOG_NUMBER>1047</CATALOG_NUMBER>
    <ITEM_NAME>Adrian3 Navy Grey Red </ITEM_NAME>
    <UNIT>KUS  </UNIT>
    <COUNTRY>CZ  </COUNTRY>
    <CURRENCY>CZK</CURRENCY>
    <PRICE_VAT>797.00</PRICE_VAT>
    <PRICE_VAT1>21.0</PRICE_VAT1>
    <NOTE>Sportovní bota v barvě...
    </NOTE>
  </ITEM>
  <ITEM>
    <CATALOG_NUMBER>106</CATALOG_NUMBER>
    <ITEM_NAME>Betty Hot pink</ITEM_NAME>
    <UNIT>KUS  </UNIT>
    <COUNTRY>CZ  </COUNTRY>
    <CURRENCY>CZK</CURRENCY>
    <PRICE_VAT>797.00</PRICE_VAT>
    <PRICE_VAT1>21.0</PRICE_VAT1>
    <NOTE>Krásná a jednoducháým...
    </NOTE>
  </ITEM>
  <ITEM_GROUPS>
    <GROUP>00010000000000000000</GROUP>
    <GROUP_NAME>Pediped</GROUP_NAME>
    <GROUP>00010001000000000000</GROUP>
    <GROUP_NAME>Original</GROUP_NAME> 
    <GROUP>00010002000000000000</GROUP>
    <GROUP_NAME>Gripngo</GROUP_NAME>
  </ITEM_GROUPS>
</ITEMS>

I want in the second part of xml i want have only once and . And up of xml i do not have ....

This is my code:

SqlConnection con = new SqlConnection("Data Source=****");
string strSQL = "select top 3 D.KOD_ZBOZI AS CATALOG_NUMBER.....";
string strSQL2 = "select [OBCH_......";
SqlDataAdapter dt = new SqlDataAdapter(strSQL, con);
SqlDataAdapter dt2 = new SqlDataAdapter(strSQL2, con);
DataSet ds = new DataSet("ITEMS");
dt.Fill(ds, "ITEM");
dt2.Fill(ds, "ITEM_GROUPS");
ds.WriteXml(("C:\\Users\\Lukas\\Desktop\\NOHEL.xml"));

Any ideas please?

Would [XSLT](http://stackoverflow.com/questions/34093/how-to-apply-an-xslt-stylesheet-in-c-sharp) transformation be an option for you? — Jozef Benikovský, Aug 22 '13 at 06:25

score 3 · Accepted Answer · answered Aug 22 '13 at 06:29

3

You can try as :

DataSet ds = new DataSet("ITEMS");

answered Aug 22 '13 at 06:29

Mohammad Arshad Alam

9,694
6
38
61

YES! One problem its OK :) – Kate Aug 22 '13 at 06:31
what is your second problem ? you did not mention it. – Mohammad Arshad Alam Aug 22 '13 at 06:35
He "sort of" mentioned his second problem. He doesn't want each ITEM_GROUPS seperately but one big ITEM_GROUPS with all the GROUP and GROUP_NAME nodes inside. - Which seems like a bad idea, because how would one know which GROUP corresponds to which GROUP_NAME. Yes, they are "ordered" in the document, but that order is arbitrary. – Corak Aug 22 '13 at 06:41
@Kate, then you can select my answer – Mohammad Arshad Alam Aug 22 '13 at 06:55

score 1 · Answer 2 · answered Aug 22 '13 at 06:24

1

Did you this:
DataSetName Property

answered Aug 22 '13 at 06:24

Mr T.

4,278
9
44
61

Export sql data to xml

2 Answers2