Converting mysql query result to xml

[carlo.gherarducci]

Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.

[dernovich]

create an xmlWriter
use the response dataset's createreader function to create a datareader
of the set
use the writer's writeStartElement method with "Solutions" as the
argument
use the writer's writeStartElement method with "Solution" as the
argument
now use the writer's writeElementString("Company" with
reader.item("Company"))
now use the writer's writeElementString("DeparturePort" with
reader.item("DeparturePort"))

and so on

Then Finish with the writer's writeEndElement()... twice (one for
Solution, and another for Solutions)

you can clean this up, make a function out of it that returns a stream,
an xmlDocument, Uri to a new file,a new dataSet or dataTable, raw
XML... whatever.

Since the writer is forward only and the datareader you use is a direct
push of the data, performance should be excelent. Its probably the
easiest way to do it, but you must set it up correctly, preferably as a
private or protected function or sub of a class in your Data layer.

On Nov 27, 9:09 am, [e-mail] wrote:

Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.

[carlo.gherarducci]

Thank you, I'll try with that.

de*******@gmail.com ha scritto:

create an xmlWriter
use the response dataset's createreader function to create a datareader
of the set
use the writer's writeStartElement method with "Solutions" as the
argument
use the writer's writeStartElement method with "Solution" as the
argument
now use the writer's writeElementString("Company" with
reader.item("Company"))
now use the writer's writeElementString("DeparturePort" with
reader.item("DeparturePort"))

and so on

Then Finish with the writer's writeEndElement()... twice (one for
Solution, and another for Solutions)

you can clean this up, make a function out of it that returns a stream,
an xmlDocument, Uri to a new file,a new dataSet or dataTable, raw
XML... whatever.

Since the writer is forward only and the datareader you use is a direct
push of the data, performance should be excelent. Its probably the
easiest way to do it, but you must set it up correctly, preferably as a
private or protected function or sub of a class in your Data layer.

On Nov 27, 9:09 am, [e-mail] wrote:

Hi all, I'm a newbie in .net xml programming, so please be patient. And
sorry for my uncorrect english, too.
I'm going to explain my problem:

I've built a web service which responds to ferries timetable requests.
It receives an xml document with this format:
<TimeTableRequest>
<Routes>
<Route>
<Company></Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<Date>2006-12-20T13:00:00</Date>
</Route>
<Route>
<Company></Company>
<DeparturePort>pio</DeparturePort>
<ArrivalPort>ptf</ArrivalPort>
<Date>2006-12-23T15:00:00</Date>
</Route>
</Routes>
</TimeTableRequest>

I deserialize the xml file to a TimeTableRequest object through xml
methods provided by C# 2005. Then I query the mysql (rel.5) database
with those parameters and I'd need back an XML file with this format:

<TimeTableResponse>
<Solutions>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-22T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-22T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
</Solutions>
</TimeTableResponse>

Querying the db, I obtain a dataset, and then the xml from it, through
the method GetXml(), but this method gave me an object like this:

<TimeTableResponse>
<Solution>
<Company>trm</Company>
<DeparturePort>ptf</DeparturePort>
<ArrivalPort>pio</ArrivalPort>
<DepartureDate>2006-12-23T05:10:00+01:00</DepartureDate>
<ArrivalDate>2006-12-23T06:10:00+01:00</ArrivalDate>
<FareRule>Bassa Stagione</FareRule>
</Solution>
...
</TimeTableResponse>

i.e. without the "Solutions" (plural) tag. I got this xml loading the
schema file (.xsd) in the dataset, but keeps ignoring that tag. How can
obtain an exact mapping to the schema?
Thanks in advance.