How to generate XML file from dataset using XSD and MVC

Question

I have an XSD file which references other XSD files. It is used by a tomcat application to generate an XML file with water sample results. In MVC application I need to query the database, get rows back, and turn them into an XML document that is formatted by referencing the XSD file. I'm at a point where the only solution I can think of is to have an existing XML file and put tags and load into a string and loop through the string replacing the tags with the row's values.

Is there anything in .NET MVC5 that can do something like this? I am stumped. Below is some relevant pieces.

==========================================

XML file at top looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<EN:eDWR xmlns:EN="urn:us:net:exchangenetwork"
    xmlns:SDWIS="http://www.epa.gov/sdwis"
    xmlns:ns3="http://www.epa.gov/xml" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <EN:Submission EN:submissionFileCreatedDate="2012-07-21"
        EN:submissionFileName="B_14271BJB.csv" EN:submissionID="1">
        <EN:LabReport>
            <EN:LabIdentification>
                <EN:LabAccreditation>
                    <EN:LabAccreditationIdentifier>OR100024</EN:LabAccreditationIdentifier>
                    <EN:LabAccreditationAuthorityName>STATE</EN:LabAccreditationAuthorityName>
                </EN:LabAccreditation>
            </EN:LabIdentification>
            <EN:Sample>
                <SDWIS:RecordID>155628</SDWIS:RecordID>
                <EN:SampleIdentification>
                    <EN:LabSampleIdentifier>123321</EN:LabSampleIdentifier>
                    <EN:PWSIdentifier>OR4100237</EN:PWSIdentifier>
                    <EN:PWSFacilityIdentifier>DIST-A</EN:PWSFacilityIdentifier>
                    <EN:SampleRuleCode>TC</EN:SampleRuleCode>
                    <EN:SampleMonitoringTypeCode>RP</EN:SampleMonitoringTypeCode>

===================================

xsd file complete. It references other xsd files in same folder:

<?xml version="1.0" encoding="UTF-8"?>
<!-- edited with XMLSPY v5 rel. 4 U (http://www.xmlspy.com) by Leslie Flagler (SAIC) -->
<xsd:schema targetNamespace="urn:us:net:exchangenetwork" xmlns:SDWIS="http://www.epa.gov/sdwis" xmlns:EN="urn:us:net:exchangenetwork" xmlns:facid="http://www.epa.gov/xml" xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="qualified" version="3.0">
    <xsd:annotation>
        <xsd:documentation/>
    </xsd:annotation>
    <xsd:include schemaLocation="./EDWR_MetaData.xsd"/>
    <xsd:include schemaLocation="./EDWR_ContactPoint.xsd"/>
    <xsd:include schemaLocation="./SDWIS_LabReport.xsd"/>
    <xsd:include schemaLocation="./EDWR_Authentication.xsd"/>
    <xsd:element name="eDWR">
        <xsd:annotation>
            <xsd:documentation>This is the standard regulatory schema
                approved by the USEPA and multi-state Lab to State
                Drinking Water Integrated Project Team</xsd:documentation>
        </xsd:annotation>
        <xsd:complexType>
            <xsd:annotation>
                <xsd:documentation>This is enfoTech EDWR schema</xsd:documentation>
            </xsd:annotation>
            <xsd:sequence>
                <xsd:element ref="EN:MetaData" minOccurs="0"/>
                <xsd:element ref="EN:Receiver" minOccurs="0"/>
                <xsd:element ref="EN:Sender" minOccurs="0"/>
                <xsd:element ref="EN:Submission"/>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
    <xsd:complexType name="SubmissionDataType">
        <xsd:annotation>
            <xsd:documentation>Transction information</xsd:documentation>
        </xsd:annotation>
        <xsd:sequence>
            <xsd:element ref="EN:LabReport" minOccurs="0" maxOccurs="unbounded"/>
            <xsd:element ref="EN:SubmissionCertification" minOccurs="0"/>
        </xsd:sequence>
        <xsd:attribute name="submissionID" type="xsd:string"/>
        <xsd:attribute name="submissionFileName" type="xsd:string"/>
        <xsd:attribute name="submissionFileCreatedDate" type="xsd:date"/>
    </xsd:complexType>
    <xsd:element name="MetaData" type="EN:MetaDataDataType">
        <xsd:annotation>
            <xsd:documentation>General information about the schema</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    <xsd:element name="Receiver" type="EN:ContactPointDataType">
        <xsd:annotation>
            <xsd:documentation>Regulatory agency and contact to receive
                the e-DWR submission file</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    <xsd:element name="Sender" type="EN:ContactPointDataType">
        <xsd:annotation>
            <xsd:documentation>The sender of the e-DWR submission file</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    <xsd:element name="LabReport" type="EN:LabReportDataType">
        <xsd:annotation>
            <xsd:documentation>Lab Analysis Report (includes Lead and
                Copper Report, Water Quality Parameters, and
                Bacteriological Analysis Report, etc)</xsd:documentation>
            <xsd:documentation>Chemical Analysis Report (includes Lead
                and Copper Report, Water Quality Parameters, and
                Bacteriological Analysis Report)</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    <xsd:element name="SubmissionCertification" type="EN:AuthenticationDataType">
        <xsd:annotation>
            <xsd:documentation>Submission Certification</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
    <xsd:element name="Submission" type="EN:SubmissionDataType">
        <xsd:annotation>
            <xsd:documentation>Information pertaining to a drinking
                water report submission</xsd:documentation>
        </xsd:annotation>
    </xsd:element>
</xsd:schema>

Does this help ? - http://stackoverflow.com/questions/6530424/generating-an-xml-file-using-xsd-file — Johnv2020, Aug 27 '15 at 18:16
Why do you need a schema when you are writing the code to generate the xml? You should validate the xml against the schema after to output the xml to a file as part of your software verification. It doesn't have to be performed dynamically. It is very simple to generate your xml using XML Linq in c#. — jdweng, Aug 27 '15 at 18:17
Johnv2020: no that doesn't help. command prompt returned a ton of errors when I tried to generate a class from the .xsd file. @jdweng: yes you might be right. I will come back from lunch and head in that direction and then get back here to respond. — JustJohn, Aug 27 '15 at 18:35

jdweng · Accepted Answer · 2015-08-28T19:51:05.283

This gives exact results. Getting started is tough, the rest is easy.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Linq;



namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        const string FILENAME = @"c:\temp\test.xml";

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string identification =
      "<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
      "<EN:eDWR" +
      " xmlns:EN=\"urn:us:net:exchangenetwork\"" +
      " xmlns:SDWIS=\"http://www.epa.gov/sdwis\"" +
      " xmlns:ns3=\"http://www.epa.gov/xml\"" +
      " xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"" +
      "/>";

            XDocument doc = XDocument.Parse(identification);
            XElement eDWR = doc.Elements().Where(x => x.Name.LocalName == "eDWR").FirstOrDefault();

            XNamespace EN = eDWR.GetNamespaceOfPrefix("EN");
            XNamespace SDWIS = eDWR.GetNamespaceOfPrefix("SDWIS");
            XNamespace ns3 = eDWR.GetNamespaceOfPrefix("ns3");
            XNamespace xsi = eDWR.GetNamespaceOfPrefix("xsi");

            DataTable dt = QueryDataBase();

            foreach (DataRow row in dt.AsEnumerable())
            {
                XElement submission = new XElement(EN + "Submission");
                submission.Add(new object[] {
                new XAttribute(EN + "submissionFileCreatedDate", row.Field<DateTime>("submissionFileCreatedDate")),
                new XAttribute(EN + "submissionFileName", row.Field<string>("submissionFileName")),
                new XAttribute(EN + "submissionID", row.Field<int>("submissionID"))
                });

                eDWR.Add(submission);


                XElement LabAccreditation = new XElement(EN + "LabIdentification", new object[] {
                    new XElement(EN + "LabAccreditationIdentifier", row.Field<string>("LabAccreditationIdentifier")),
                    new XElement(EN + "LabAccreditationAuthorityName", row.Field<string>("LabAccreditationAuthorityName"))
                });

                XElement labReport = new XElement(EN + "LabReport");
                XElement labIdentification = new XElement(EN + "LabIdentification");
                submission.Add(labReport);
                labReport.Add(labIdentification);
                labIdentification.Add(LabAccreditation);

                XElement sampleIdentification = new XElement(EN + "SampleIdentification", new object[] {
                    new XElement(EN + "LabSampleIdentifier", row.Field<int>("LabSampleIdentifier")),
                    new XElement(EN + "PWSIdentifier", row.Field<string>("PWSIdentifier")),
                    new XElement(EN + "PWSFacilityIdentifier", row.Field<string>("PWSFacilityIdentifier")),
                    new XElement(EN + "SampleRuleCode", row.Field<string>("SampleRuleCode")),
                    new XElement(EN + "SampleMonitoringTypeCode", row.Field<string>("SampleMonitoringTypeCode"))
                 });

                XElement sample = new XElement(EN + "Sample");
                labReport.Add(sample);
                XElement RecordID = new XElement(SDWIS + "RecordID", row.Field<int>("RecordID"));
                sample.Add(RecordID);
                sample.Add(sampleIdentification);


            }

            doc.Save(FILENAME);

        }
        public DataTable QueryDataBase()
        {
            DataTable dt = new DataTable();

            //Here is an example of getting a datatable from a database
            string SQL = "Enter Your SQL Here";
            string connStr = "Enter your database connection string here";

            //uncomment instructions below
            //SqlDataAdapter adapter = new SqlDataAdapter(SQL, connStr);
            //adapter.Fill(dt);


            //I will build a dummy datatable for your test case
            dt.Columns.Add("submissionFileCreatedDate", typeof(DateTime));
            dt.Columns.Add("submissionFileName", typeof(string));
            dt.Columns.Add("submissionID", typeof(int));
            dt.Columns.Add("LabAccreditationIdentifier", typeof(string));
            dt.Columns.Add("LabAccreditationAuthorityName", typeof(string));
            dt.Columns.Add("RecordID", typeof(int));
            dt.Columns.Add("LabSampleIdentifier", typeof(int));
            dt.Columns.Add("PWSIdentifier", typeof(string));
            dt.Columns.Add("PWSFacilityIdentifier", typeof(string));
            dt.Columns.Add("SampleRuleCode", typeof(string));
            dt.Columns.Add("SampleMonitoringTypeCode", typeof(string));


            dt.Rows.Add(new object[] {
                DateTime.Parse("2012-07-21"),
                "B_14271BJB.csv",
                1,
                "OR100024",
                "STATE",
                155628,
                123321,
                "OR4100237",
                "DIST-A",
                "TC",
                "RP"
            });


            return dt;
        }

    }
}

So I will accept this as the answer but I don't know how to create the XML file and save it in a location. I also don't know what (string[] args) does. And I have other nested nodes or whatever below submission. Is there anyway to carry on a dialogue offline? Here is the end of my xml example that includes the nested nodes except for one. — JustJohn, Aug 28 '15 at 16:58
And. . .How do I query the table in database to get rows, of which there will be more than one (all values are in each row) and of which all Sample elements are stacked together then all AnalyticalResults stacked together below that. — JustJohn, Aug 28 '15 at 17:01
I can get the code to run in a static void using a button click. To try and wrestle this one into submission how would I print out or save the XML generated from the code you have supplied? — JustJohn, Aug 28 '15 at 18:34
I updated my code to show an example of getting data from a database. The query to the database may be very complex, may require a JOIN statement, and may end up in multiple datatables. My code is very simple, but should get you started. — jdweng, Aug 28 '15 at 19:37
Yes! Thank you very much! I worked all afternoon on getting the other XML elements in the code you provided me once I understood how the tree works, got a button click that builds the file and saves it and it looks just like the ones we use. Not to worry about JOINS. In fact this project just uses all info from one table. Your data query should work fine. I will have to loop through twice, once for sample entity and once for results entity. The application wants to take all sample info (the water characteristics) first, then analytical results. Have a great weekend! I plan to. — JustJohn, Aug 28 '15 at 23:38

How to generate XML file from dataset using XSD and MVC

XML file at top looks like this:

xsd file complete. It references other xsd files in same folder:

1 Answers1