2

I have a problem parsing XML input. I get a string of all Authors while I only want the distinct author or first value.

+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| doc_id   | xml_doc   
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                                                                                                                                                                                                                                                           |
| 1        | <catalog><book id="11"><author>Chuck Palahniuk</author><title>Fight Club</title></book><book id="12"><author>Chuck Palahniuk</author><title>Survivor</title></book></catalog>                                                                                        |
| 2        | <catalog><book id="21"><author>Bernard Werber</author><title>Les Thanatonautes</title></book></catalog>                                                                                                                                                              |
| 3        | <catalog><book id="31"><author>Boris Vian</author><title>The Big Sleep</title></book><book id="32"><author>Boris Vian</author><title>The Lady in the Lake</title></book><book id="33"><author>Boris Vian</author><title>The World of Null-A</title></book></catalog> |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


SELECT ExtractValue(xml_doc, '//author[1]') AS author FROM catalogs ORDER BY author;

OUTPUT:

+----------------------------------+
|              author              |
+----------------------------------+
| Bernard Werber                   |
| Boris Vian Boris Vian Boris Vian |
| Chuck Palahniuk Chuck Palahniuk  |
+----------------------------------+

While I only would like distinct xml. Can this be achieved somehow? Or am I missing something?

My expected result would be

+----------------------------------+
|              author              |
+----------------------------------+
| Bernard Werber                   |
| Boris Vian                       |
| Chuck Palahniuk                  |
+----------------------------------+

2 Answers 2

2

To get the first author of the first book, you can use

select ExtractValue(xml_doc, '//book[1]//author[1]');

To get a real distinct list you would have to write a stored function that circles through all books and all authors.

1
  • This fixed my issue
    – Ludisposed
    Jul 21, 2017 at 11:37
-1

You can try this.

SELECT
  SUBSTRING_INDEX(
    ExtractValue(
      REPLACE(artists, '</author>', '+</author>'), 
      '//author[1]'
    ), '+', 1
  ) AS Author
FROM music.catalogs
ORDER BY author;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.