Create a view from an XML field that one row per node in the XML field--is this possible?

[Joshua Beall]

Hi All,

Let's say I've got some XML field like this:

<xml>
<Person>
<FirstName>John</FirstName>
<LastName>Smith</LastName>
</Person>
<Person>
<FirstName>Jane</FirstName>
<LastName>Johnson</LastName>
</Person>
</xml>

There are an arbitrary number of Person elements, each with FirstName and LastName child nodes.

I would like to turn that into a view that looks like this:


+---------------------------------+
| FirstName | LastName |
+---------------------------------+
| John | Smith |
+---------------------------------+
| Jane | Johnson |
+---------------------------------+

Is it possible to create a MySQL view from XML data that returns an variable number of rows depending on the contents of the XML field it is pulling from?

-Josh