XML ADO Approach

[pit...@gmail.com]

Hello,

I am faced with a problem that may be simple to you. However I am stuck
as to how to approach it. I have a table with employee information.
This table needs to be dumped to an xml file in specific format. To be
precise I need to create a SPML file. SPML is standard XML format for
things. At any rate I am not sure how to go about this. Here is what I
am thinking I might do. I am just looking for some general
confirmations as I am new to this.

Create a XSD or Typed Dataset.
Fill it the dataset with the data in the table.
Write that DataSet to a file.

Is this the correct approach?

Thanks,
PD

[W.G. Ryan - MVP]

I'm not familiar with SPML as such but if it's well formed XML then using
.WriteXML should work for you. the only thing I'd mention is that you have
relatively limited control over the format of the XML that comes out of a
dataset. however you can easily work around that by using the GetXML method
and then using an XSLT transformation to make the XML look like whatever you
need it to. Personally, I had a little trouble when i first learned XSLT as
opposed to most other facets of XML programming but even with the trouble I
had, after about 10 serious hours of working with it, I got to a point that
I could do most of what I wanted (and to be honest, i was going from XML to
HTML/CSS and a big part of my trouble was with the HTML/CSS components).
After another 10 hours, I pretty much got to the point that I can
comfortably assume that whatever I need to transform the XML to, i can get
it done. 20 hours isn't 'nothing' but once you get over the learning curve,
the possibilities are endless and doing it in .NET is pretty straightforward
(amazingly, it's probably quite a bit easier than learning to use the old
System.Data.XMl libs for writing your xml documents).

if I can be of help, please let me know.
<pit...@gmail.com> wrote in message
news:1139429813.0...@g43g2000cwa.googlegroups.com...

[pit...@gmail.com]

Hey Ryan great post! That helped a lot. As awsome there are some follow
ups :-).

Based on your comments can I assume that the best way to transform my
data to the XML format I need
is to us an XLS transformation? Any nice links on the top of you head
that helped you?

Thanks,
PD

[W.G. Ryan - MVP]

Thanks man. Comments inline
<pit...@gmail.com> wrote in message
news:1139435768.3...@g14g2000cwa.googlegroups.com...

> Hey Ryan great post! That helped a lot. As awsome there are some follow
> ups :-).
>
> Based on your comments can I assume that the best way to transform my
> data to the XML format I need
> is to us an XLS transformation?

--Assuming that you need to change the layout, than yes, XSLT is the way to
go. plus, learning XSLT is a skill that doesn't just apply to .NET so you
can apply it in other areas. But XSLT is a way that you can take XML and
transform it into any other XML you want it to look like or text or HTML

Any nice links on the top of you head
> that helped you?

--The documentation on MSDn was a lifesaver for me

http://msdn.microsoft.com/msdnmag/issues/0500/xml/
http://msdn.microsoft.com/msdnmag/issues/02/03/xml/
http://search.msdn.microsoft.com/search/results.aspx?qu=XSLT&View=msdn&st=b
http://www.w3.org/TR/xslt

>
> Thanks,
> PD
>

[Cor Ligthert [MVP]]

PitDog,

Be aware that XML is a description format.
A dataset is a collection class, that holds datatables, which holds datarow,
which holds items.
(Beside that holds the dataset a collection of relations)

For those is in ADONET a standard method to read/write it to an extern
medium. ReadXML and WriteXML.

Don't mix the dataset automaticly up with a XML files, there are a lot more
types of XML files, although probably non of them so much standard as the
dataset.

To create a dataset you can use designer tools where XSD is in fact the
template, or you can do it by yourself, which can be in VBNet

\\\
dim ds as new dataset
dim dt as new datatable
ds.tables.add(dt)
dt.columns.add("MyName")
dt.loadDataRow(new object() {"Cor"),true)
ds.writeXML(pathtodisk)
///
This is an untyped dataset.

If you use the XSD than you can generate an typed dataset. Be aware that
those are with the versions 1.x and 2.0 completely different and don't even
exist anymore in webapplications at the moment.

I hope this helps,

Cor

[Darren Kopp]

I was recently approached by a friend who had an xml problem. The XML
was as follows

<locations>
<state>
<statename>Utah</statename>
<county>
<countyname>Davis</countyname>
<city>
<cityname>Bountiful</cityname>
</city>
</county
</state>
</locations>

This is the basic structure, however there were many state elements,
many county elements, and so on. We were using a dataset at first, but
we were having creating the Master-Details approach we were looking
for. What would happen is we would load the xml into the dataset, but
it would just add all the states to a state table, all the counties to
a county table, etc.

I believe that the problem could be solved through the DataSet
relations, or through the schema, but I am a bit fuzzy in those areas
(if that would be a solution it would be great if someone let me know).
What I basically did was mapped each element onto a class.

What I did was create a locations class, with a generic list of states
(List<State> where State is the state class). Then i created a State
class which has a property StateName and had a generic list of counties
(List<County> where Country is the county class). I did the same thing
with the county class as i did with the state class, with the county
class having a generic list of cities. The city class only had a city
name property.

When we created a locations object, we would open the xml document, and
then start going through and getting the data that we needed. the root
variable is an xmlnode set to the locations element (<locations>).

We then check to see if there are child nodes (<state> elements). If
there are state elements, we loop through the state elements and add a
new state object to the list. when we add the state object to the
list, we pass the state element as an xml node to the state constructor
(root.ChildNodes[i]).

In the state constructor, we check to see if the state element has
children. If it does, then we add the state name to the state object
(StateElement.FirstChild.InnerText). We then loop through all of the
county elements (if any) and add a new county to the list, passing the
county element as an xml node to the county constructor
(StateElement.ChildNodes[i]). Note that we initialize i = 1. For i =
0, the xml node would be StateName, i=1 gets the County element.

In the county constructor, we check to see if the county element has
any children (<countyname> and <city>). If there are, then we add the
state name to the county object. We then loop through all of the city
elements (if any) and add a new city to the cities list, passing the
city element as an xml node to the city constructor
(CountyElement.ChildNodes[i]). Note that we initialize i = 1. For i =
0, the xml node would be CountyName, i=1 gets the City element.

Finally, in the city constructor, we check to see if the CityElement
has children (<cityname>). If it does, then we add the cityname to the
object.

This allowed me to get the populate all the data in the xml. We could
then handle all of the changes (such as adding a state, or removing a
county, etc) with the locations object in memory. When we were
finished we could write the xml back out to the file by calling the
WriteXML() method on each object.

We create the XmlTextWriter in the WriteXML() method in the locations
class, and pass it by reference to the WriteXML() method in the state
class, which passes the writer by reference to the county class and so
on. The locations WriteXML() method writes the start of the document,
and the writes <location>. Then we loop through each state in the
list, calling the WriteXML() method on the state object.

The WriteXML() method in the state class is similar to that of
location, but we use the writer that is passed in. This in effect just
appends what we have already written. In this method we write out the
<state> element, and the <statename> element, and then loop through
each county.

The WriteXML() method on the county and city classes are similar to
that of state. As everything loops through, we end up back in the
location WriteXML() method, which closes the <location> element, closes
the document, and then closes the XmlTextWriter, saving the document.

You could do something similar to creating an employee class, as well
as some sort of EmployeeList class that has a list of employees
(List<Employee>). You can create new classes for each element that
have their own children (such as my friends did) and then have class
variables for elements with values (such as <cityname>Woods
Cross</cityname>).

I hope this helps you out somehow. Here is the code that we used.

public class Locations
{
#region "Private Variables"

private List<State> _States;

#endregion

#region "Public Properties"

public List<State> States
{
get { return _States; }
set { _States = value; }
}

#endregion

/// <summary>
/// Populates a Locations object form data stored in an XML
document
/// </summary>
/// <param name="XMlDoc">String. Path of the XML Document with
information about Locations</param>
public Locations(string XMlDoc)
{
_States = new List<State>();

XmlDocument doc = new XmlDocument();
doc.Load(XMlDoc);

// get the first element (locations)
XmlElement root = doc.DocumentElement;

// check to see if there are any elements in the document
if (root.HasChildNodes)
{
// loop through the states
for (int i = 0; i < root.ChildNodes.Count; i++)
{
_States.Add(new State(root.ChildNodes[i]));
}
}
}

public void WriteXML()
{
XmlTextWriter writer = new XmlTextWriter("../../States.xml",
System.Text.Encoding.UTF8);
writer.Formatting = Formatting.Indented;
writer.Indentation = 4;

// write out <?xml version="1.0" encoding="utf-8" ?> to file
writer.WriteStartDocument(false);

// begin writing the location element
writer.WriteStartElement("Locations");

// loop through each state element
foreach (State st in States)
{
st.WriteXML(ref writer);
}

// close the location element
writer.WriteEndElement();

// close the document
writer.WriteEndDocument();

// close the writer
writer.Close();
}
}

public class State
{
#region "Private variables"
private string _Name;
private List<County> _Counties;
#endregion

#region "Public Properties"
public string StateName
{
get { return _Name; }
set { _Name = value; }
}
public List<County> Counties
{
get { return _Counties; }
set { _Counties = value; }
}
#endregion

/// <summary>
/// Overloaded. Populates a state object with data from an xml
node.
/// </summary>
/// <param name="StateElement">An XmlNode with data from a State
element</param>
public State(XmlNode StateElement)
{
// Initialize the county list
_Counties = new List<County>();

// Expects an xml node with the following format
//
// <State>
// <StateName>Text</StateName>
// <County>CountyElement</County>
// </State>

if (StateElement.HasChildNodes)
{
_Name = StateElement.FirstChild.InnerText;

for (int i = 1; i < StateElement.ChildNodes.Count; i++)
{
_Counties.Add(new County(StateElement.ChildNodes[i]));
}
}
}

public void WriteXML(ref XmlTextWriter writer)
{
// begin writing the state element
writer.WriteStartElement("State");

// write the StateName element
writer.WriteElementString("StateName", StateName);

// loop through each county
foreach (County co in Counties)
{
co.WriteXML(ref writer);
}

// close the State element
writer.WriteEndElement();
}
}

public class County
{
#region "Private variables"
private string _Name;
private List<City> _Cities;
#endregion

#region "Public Properties"
public string CountyName
{
get { return _Name; }
set { _Name = value; }
}
public List<City> Cities
{
get { return _Cities; }
set { _Cities = value; }
}
#endregion

/// <summary>
/// <para>Overloaded. Populates a County object from a county
element that is
/// located in an xml file</para>
/// </summary>
/// <param name="CountyElement">An XmlNode that contains the data
from a CountyElement</param>
public County(XmlNode CountyElement)
{
// initialize the cities list
_Cities = new List<City>();
// Begin to get the list of cities for the county
// Expects an xml node with format similar to following
//
// <County>
// <CountyName>Text</CountyName>
// <City>[CityElement]</City>
// </County>
if (CountyElement.HasChildNodes)
{
// get the country name from the CountyName element
_Name = CountyElement.FirstChild.InnerText;

for (int i = 1; i < CountyElement.ChildNodes.Count; i++)
{
// add the city to the city list
_Cities.Add(new City(CountyElement.ChildNodes[i]));
}
}
}

public void WriteXML(ref XmlTextWriter writer)
{
// begin writing the state element
writer.WriteStartElement("County");

// write the StateName element
writer.WriteElementString("CountyName", CountyName);

// loop through each county
foreach (City ct in Cities)
{
ct.WriteXML(ref writer);
}

// close the State element
writer.WriteEndElement();
}
}

public class City
{
#region "Private variables"
private string _Name;
#endregion

#region "Public Properties"
public string CityName
{
get { return _Name; }
set { _Name = value; }
}
#endregion

public City(XmlNode CityElement)
{
// The node will consist of something similar to the following
//
// <City>
// <CityName>Text</CityName>
// </City>
if (CityElement.HasChildNodes)
{
// get the city name from the CityName element
_Name = CityElement.FirstChild.InnerText;
}
}

public void WriteXML(ref XmlTextWriter writer)
{
// begin writing the city element
writer.WriteStartElement("City");

// write the CityName element
writer.WriteElementString("CityName", CityName);

// close the City element
writer.WriteEndElement();
}
}