I was recently tasked with some ETL related activity.
The objective was to get data from a table to a hierarchical XML file.
This is how I went about it.
1> Get the XSD File.
2> Map the elements in the XSD with our present data.
3> Use XSD tool to create a class file.
4> Use Visual Studio to include this class in a project.
5> Create an instance of the class and then write the XML.
I have simplified the code by excluding the DB connection and hardcoding the values.
Code:
<?xml version="1.0" encoding="utf-8"?>
<xs:schema id="RECORDSET" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="RECORDSET" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="STUDENT_RECORD">
<xs:complexType>
<xs:sequence>
<xs:element name="firstname" type="xs:string" minOccurs="0" />
<xs:element name="lastname" type="xs:string" minOccurs="0" />
<xs:element name="dob" type="xs:string" minOccurs="0" />
<xs:element name="subjectlist" minOccurs="0" maxOccurs="1">
<xs:complexType>
<xs:sequence>
<xs:element name="subject" type="xs:string" nillable="true" minOccurs="0" maxOccurs="unbounded" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
2> Open the visual studio command line window and type
PHP Code:
xsd /c /l:cs /n:StudentXSD student.xsd
You'll be able to get a C Sharp file.
3> Add reference to this C Sharp file in the Visual Studio Environment.
4> The following code will help you create a XML file based on this structure.
c# Code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Xml.Serialization;
using System.IO;
using System.Linq;
using System.Text;
using StudentXSD;
using System.Windows.Forms;
namespace StudentXSD
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void cmdWriteXML_Click(object sender, EventArgs e)
{
RECORDSETSTUDENT_RECORD rc1 = new RECORDSETSTUDENT_RECORD();
string[] subjects = { "math", "science", "nuclear physics"};
rc1.firstname = "Adam";
rc1.lastname = "Cross";
rc1.dob = DateTime.Today.ToShortDateString();
rc1.subjectlist = subjects;
SerializeToXML(rc1);
}
static public void SerializeToXML(RECORDSETSTUDENT_RECORD studRec)
{
XmlSerializer serializer = new XmlSerializer(typeof(RECORDSETSTUDENT_RECORD));
TextWriter textWriter = new StreamWriter(@"D:\User\studentData.xml");
serializer.Serialize(textWriter, studRec);
textWriter.Close();
}
}
}