0

is there a way to retrieve the result of a query / or to dump a whole table into an XML fragment which can be retrieved by using an XML query? I know there is something like this for PostgreSQL (9.0), table_to_xml()and query_to_xml().

I also know that mysqldump --xml can export XML, but I do seek for something that allows me to issue a simple query. The application I’m working on should allow some users to dump a certain table into an XML file on their machine, therefor I need to issue a query and obtain a String or something (is there an XML type in MySQL?).

I need the result to be XML and a Result Set of a query, not a file on server or something.

scravy
  • 11,904
  • 14
  • 72
  • 127

3 Answers3

0

The query resulting in a SQL script similar to a MySQL dump for a single table would have three parts:

  1. SHOW CREATE TABLE tblname - to generate CREATE TABLE statement.
  2. DESCRIBE tblname - to retrieve column names for the construction of INTO(...) part of the INSERT queries.
  3. SELECT * FROM tblname - to retrieve values for the construction of VALUES(...) part of the INSERT queries. Each row in the result set will correspond to an INSERT statement. INSERT statements will be generated in the loop handling the result set.

If this is to be done from MySQL, it can be wrapped into a stored procedure.

Vlad
  • 1,723
  • 12
  • 16
0

Found this in a question here at stackoverflow, as linked in the comments. Proposes to manually build XML in a query, like

 SELECT concat("<this-is-xml>", field1, "</this-is-xml>") FROM ...

Of course, xml-charcter escaping and so on has to be done manually.

There seems to be no native way to directly get the result of a query as xml.

There is also a library (lib_mysqludf_xql) for mysql which provides XML functionality for MySQL.

Community
  • 1
  • 1
scravy
  • 11,904
  • 14
  • 72
  • 127
-1

INTO OUTFILE will dump the results to an XML file, so you could then send that to a client.

Cylindric
  • 5,858
  • 5
  • 46
  • 68
  • I need the result to be a result set of a query, not a file on the server. btw, I thought INTO OUTFILE dumps CSV? – scravy Dec 01 '11 at 17:57
  • Ah yes, I think you are correct - I mis-read some articles, but they were using some custom functions, or the mysql command-line option `-X`. – Cylindric Dec 01 '11 at 18:05