0

I'm trying to extract value from a column containing xml.

here's a snippet from my xml, where i want the value of "fieldscreen.id"

<action id="141" name="Abandonner" view="fieldscreen">
          <meta name="issue.editable">false</meta>
          <meta name="description">Abandonnée</meta>
          <meta name="fieldscreen.id">10102</meta>
          <restrict-to>
            <conditions>
              <condition type="class">

Please help me with the sql where i get only the value of fieldscreen.id from the column. Database- mysql

Ritika
  • 1
  • Does this answer your question? [MySql query to retrieve value of element attribute of xml](https://stackoverflow.com/questions/29343016/mysql-query-to-retrieve-value-of-element-attribute-of-xml) – Mark Carpenter Jr May 14 '20 at 15:15

1 Answers1

2
set @xml:='<action id="141" name="Abandonner" view="fieldscreen">\n<meta name="issue.editable">false</meta>\n<meta name="description">Abandonnée</meta>\n<meta name="fieldscreen.id">10102</meta>\n</action>';

SELECT ExtractValue(@xml, 'action/meta[@name="fieldscreen.id"]');

| ExtractValue(@xml, 'action/meta[@name="fieldscreen.id"]') |
| :-------------------------------------------------------- |
| 10102                                                     |

db<>fiddle here

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Hi Akina, when i run the query for my column in database which has full xml, it gives null result. – Ritika May 15 '20 at 03:44
  • @Ritika *when i run the query for my column in database which has full xml, it gives null result.* Edit my fiddle, insert 3-5 YOUR REAL record values instead of example value, and give new link - 'll look at... – Akina May 15 '20 at 04:22
  • https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2c4ebd602783b104b93f9d11cb951ea8 – Ritika May 17 '20 at 17:37
  • @Ritika 1) Always check for quote chars in string literals, and quote them (double, for example). 2) There is NO entity with the name "fieldscreen.id" in your XML - but there are 6 entities with the name "jira.fieldscreen.id" and more complex paths. – Akina May 17 '20 at 20:10