473,394 Members | 1,264 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

Converting mysql query result to xml

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.

Nov 27 '06 #1
2 3210

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, carlo.gherardu...@gmail.com 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.
Nov 28 '06 #2
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, carlo.gherardu...@gmail.com 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.
Nov 29 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Bruce A. Julseth | last post by:
Newbie Question: Is there a way to import MS Access into MySQL? Maybe create a CSV or something. If so, what would be the SQL to do this? Thanks... Bruce
2
by: mike m | last post by:
all, was able to piece together the following php/html code. Basically I want to push an SQL query and format it, present it to an html document. The problem appears with the SQL query. ...
2
by: Phil Powell | last post by:
Relevancy scores are normally defined by a MySQL query on a table that has a fulltext index. The rules for relevancy scoring will exclude certain words due to their being too short (minimum...
0
by: Phil Powell | last post by:
The table already has a fulltext index and from there I can use the MySQL fulltext search query to get results as well as the relevancy score. The problem I have is that MySQL has a default...
4
by: Mark Wilson CPU | last post by:
A colleague has written a prototype program in PHP, using a MySQL database. It's a relatively simple app, with a restricted set of mysql commands used (see below). The MySQL DB is being replaced...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
3
by: dean.elwood | last post by:
Hi guys, My first post here and I'm a pascal coder doing his best to move to C/C++ so please go easy on me ;) I'm using the MySQL C API to pull some values from a DB. One of the fields is a...
15
by: harvey | last post by:
How do I make PHP create a database for mysql please? I can see how to make tables and I have read all the documents I can find but I don't understand how to make the database itself. All...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.