I want to have the results of this select as multiple rows NOT as a single space-delimited string.
SET @xml = '<?xml version="1.0" encoding="UTF-8"?>
<matchmaker_data>
<Models><model display_name="MyModel1" id="1" thumb="/images/MyModel1.jpg">
</model><model display_name="MyModel2" id="2" thumb="/images/MyModel2.jpg">
</model>
</Models>
</matchmaker_data>';
SELECT
ExtractValue(@xml, '//matchmaker_data/Models/model/@display_name') AS model,
ExtractValue(@xml, '//matchmaker_data/Models/model/@thumb') AS thumb;
The reult I get now:
+----------------------------------------------------------------+
| model | thumb |
+----------------------------------------------------------------+
| MyModel1 MyModel2 | /images/MyModel1.jpg /images/MyModel2.jpg |
+----------------------------------------------------------------+
Desired result:
+---------------------------------+
| model | thumb |
+---------------------------------+
| MyModel1 | /images/MyModel1.jpg |
+---------------------------------+
| MyModel2 | /images/MyModel2.jpg |
+---------------------------------+
Thanks in advance