extract value from column(contains xml) in mysql

Question

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

Does this answer your question? MySql query to retrieve value of element attribute of xml — Mark Carpenter Jr
– Mark Carpenter Jr, Commented May 14, 2020 at 15:15

Akina · Accepted Answer · 2020-05-14 13:22:18Z

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

answered May 14, 2020 at 13:22

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ritika Over a year ago

Hi Akina, when i run the query for my column in database which has full xml, it gives null result.

Akina Over a year ago

@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...

Ritika Over a year ago

dbfiddle.uk/…

Akina Over a year ago

@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.

Collectives™ on Stack Overflow

extract value from column(contains xml) in mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related