How to select XML attribute - Converting MSSQL to MySQL

I need to convert the MSSQL query below to MySQL. The query selects the value of the "id" attribute out of the "page" element in the xpath. I would expect the MySQL query to return 3 rows with the values 1,2,3 respectively. Does anybody know how to do this in MySQL? Please let me know.

开发者_如何学JAVADECLARE @InputXML as xml
SET @InputXML = '<pages><page id="1"/><page id="2"/><page id="3"/></pages>'


SELECT 
     Node.value('@id', 'Bigint') AS ID
     FROM @InputXML.nodes('/pages/page') TempXML (Node))

EDIT: forget what I said earlier.

Courtesy of Dave, you probably want to look at this:

http://dev.mysql.com/doc/refman/5.1/en/xml-functions.html

(previous answer left in place for reference):

MySQL doesn't do XML. So the short answer is: "you can't".

The long answer is, depending on the context this runs in, you have a few options.

1. Normalize your data into a proper SQL schema, instead of relying on XML inside SQL.
2. Pull the XML from the database as plain strings, and do the XML processing in your logic (PHP, C#, VB, whatever it is you're using). Unfortunately, this means you can't filter your data by xpath expressions on the database, so you'll have to pull in all candidate rows and then discard the ones you don't want.
3. Ditch the database altogether (at least for this part) and just store XML files. If storing and retrieving XML files is all your application does, and you're not adding any meta-information, then the database has little to no benefit.