By smiletrl on

Hi, all
In mssql ,'select * from mytable for xml path' will output the result as xml, while in mysql, it seems complicated according to http://stackoverflow.com/questions/2847674/xml-output-from-mysql and http://web.archive.org/web/20100212101622/http://dev.mysql.com/tech-reso...

Also, according to http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html, is this the only handy way to get xml output in Mysql? I mean 'It is possible to obtain XML-formatted output from MySQL in the mysql and mysqldump clients by invoking them with the --xml option'.

Anyhelp will be appreciated, thank you!

⋅ Categories: Drupal 7.x

Comments

nevets’s picture

nevets commented

I have never tried this, though I found http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/xml-in-mysql5.1-6.0....

Why do you want to output XML and from what tables? (There is an add-on to views that outputs XML but only for data/tables views is aware of).

smiletrl’s picture

smiletrl commented

Thanks nevets,
Actually, this needs come from a piece of mssql code. In mssql code, it collects data as xml format as a column when create a view.
In mysql, kind of like this:

CREATE VIEW myview
AS
SELECT event.End_date as date, (SELECT people.Last_name as name  from people INNER JOIN  ... WHERE ... <strong>FOR XML PATH('') </strong>)as person, 
FROM  event 
INNER JOIN ...
WHERE ...

<strong>FOR XML PATH('') </strong> This part comes from mssql. From my understanding, it will format the subquery results as xml, and then this xml data will be used as a colum in view. Because this subquery will return many rows (This makes sense why this subquery uses xml format to collect data), if we ingore this xml code, there'll be problems im mysql, saying the subquery has more than one rows.
On the other hand,
When SELECT event.End_date as date returns one row, the subquery will return more than one rows.

Is there any solution for this? Thank you.

nevets’s picture

nevets commented

I have to wonder if there is a more Drupal approach to what you are trying to do. "Translating" existing code to use in Drupal is not always the best approach.

smiletrl’s picture

smiletrl commented

Yeah, I guess you're right about this. It's not a good idea to translate code into new applications. We will try other ways to do this. Thank you.

redsd’s picture

redsd commented

Drupal isn't a db specific code that it uses, I think this is not possible to do without using a bit of php, meaning probally a lot more query request and for preformance a table with all the processed info in it.

smiletrl’s picture

smiletrl commented

Thanks redsd,
Yeah, we basically want to achieve this in mysql level, since mssql could do this with such limitted code. Problem is this task in mysql is not as simple as mssql.
Your concern about the performance for a view/table requiring a lot query should be considered. If we use php, this part of work will move from mysql to drupal. I'm not sure how to do this right now. Perhaps, change some logic in drupal.
Anyway, thank you for discussion.