sql.netxmldatasetexport-to-xml

From database to XML with data types


I am getting info from a database and adding it to a dataset. Then I create a XML with this info. I would want to have the XML, but also the datatypes in the header. Is it possible? Here my code to write the XML:

//Fill dataset with different datatable:
//first datatable
DataSet imports = new DataSet("import");
NpgsqlDataAdapter daImport = new NpgsqlDataAdapter("select field1, field2 field3 from table1", _connPg);
daImport.FillSchema(imports, SchemaType.Source, "table1");
daImport.Fill(imports, "table1");
//seconda datatable

NpgsqlDataAdapter daImport1 = new NpgsqlDataAdapter("select field1, field2 field3 from table2", _connPg);
daImport1.FillSchema(imports, SchemaType.Source, "table2");
daImport1.Fill(imports, "table2");

//insert relation
DataRelation relation = new DataRelation("rel_rel1", imports.Tables["table1"].Columns["field1"], imports.Tables["table2"].Columns["field2"], true);
relation.Nested = true;
imports.Relations.Add(relation);
//write xml
imports.WriteXml("dataImport.xml");

And this is my xml

<?xml version="1.0" standalone="yes"?>
    <import>
    <table1>
    <field1>1
    </field1>
    <field2>name
    </field2>
    <field3>surname
    </field3>
    <table2>
    <field1>somedata
    </field1>
    <field2>1
    </field2>
    <field3>otherdata
    </field3>
    </table2>
    </table1>
    </import>

What I want? I would want to get this XML but with the types of the fields defined in the header.

Thanks!


Solution

  • If you want the schema to be serialized with the data, just change :

    imports.WriteXml("dataImport.xml");
    

    to :

    imports.WriteXml("dataImport.xml", XmlWriteMode.WriteSchema);
    

    It will produce the following XML :

    <?xml version="1.0" standalone="yes"?>
    <import>
      <xs:schema id="import" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
        <xs:element name="import" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="table1">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="field1" type="xs:int" />
                    <xs:element name="field2" type="xs:string" minOccurs="0" />
                    <xs:element name="field3" type="xs:boolean" minOccurs="0" />
                    <xs:element name="table2" minOccurs="0" maxOccurs="unbounded">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="field1" type="xs:int" />
                          <xs:element name="field2" type="xs:int" minOccurs="0" />
                          <xs:element name="field3" type="xs:double" minOccurs="0" />
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
          <xs:unique name="table2_Constraint1" msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true">
            <xs:selector xpath=".//table2" />
            <xs:field xpath="field1" />
          </xs:unique>
          <xs:unique name="Constraint1" msdata:PrimaryKey="true">
            <xs:selector xpath=".//table1" />
            <xs:field xpath="field1" />
          </xs:unique>
          <xs:keyref name="FK_T1T2" refer="Constraint1" msdata:IsNested="true">
            <xs:selector xpath=".//table2" />
            <xs:field xpath="field2" />
          </xs:keyref>
        </xs:element>
      </xs:schema>
      <table1>
        <field1>0</field1>
        <field2>test</field2>
        <field3>false</field3>
        <table2>
          <field1>0</field1>
          <field2>0</field2>
          <field3>0</field3>
        </table2>
        <table2>
          <field1>1</field1>
          <field2>0</field2>
          <field3>1.25</field3>
        </table2>
      </table1>
      <table1>
        <field1>1</field1>
        <field2>test 2</field2>
        <field3>true</field3>
      </table1>
      <table1>
        <field1>2</field1>
        <field2>test 3</field2>
        <field3>false</field3>
        <table2>
          <field1>2</field1>
          <field2>2</field2>
          <field3>2.66</field3>
        </table2>
      </table1>
    </import>