Select query from Text field with XML data in MySQL

Question

i have XML data stored in a text column in MySQL table, i want to fetch the child notes with a select query but not getting any results back.

data in the table

<Attributes>
  <Map>
    <entry key="accessRequired" value="Yes"/>
    <entry key="company" value="0001"/>
    <entry key="companyName" value="ABC Inc"/>
    <entry key="contractorEndDate" value="11/15/2020"/>
    <entry key="contractorStartDate" value="10/10/2019"/>
    <entry key="costCenter" value="1234"/>
    <entry key="costCenterName" value="design &amp; develop"/>
    <entry key="country" value="US"/>
    <entry key="departmentAdminEmail" value="[email protected]"/>
      <value>
        <Map>
          <entry key="Rule" value="123456hgfjuykuiykui"/>
        </Map>
      </value>
    </entry>
  </Map>
</Attributes>

Query i am using to read the data

select display_name, SUBSTRING_INDEX(ExtractValue(attributes,'/attributes/map/departmentAdminEmail'),' ',1) from table1 where display_name like 'John%';

wanted to list the email address field only, can you guys help me out with the query.

Barmar · Accepted Answer · 2020-11-03 20:16:03Z

1

Your Xpath is looking for a <departmentAdminEmail> element. To search for an element with an attribute value you have to use [@key="departmentAdminEmail"]

And to extract the value of an attribute, you need to use /@value.

So it should be

select display_name, ExtractValue(attributes,'/attributes/map/entry[@key="departmentAdminEmail"]/@value') AS email
from table1 
where display_name like 'John%';

See XPath to select Element by attribute value and MySql query to retrieve value of element attribute of xml

edited Nov 3, 2020 at 20:16

answered Nov 3, 2020 at 20:05

Barmar

789k57 gold badges555 silver badges669 bronze badges

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

3 Comments

sqllearner Over a year ago

Thanks Barmar, i am still getting the blank results from the query. the column data type is text and XML data is stored in it not sure if this has to do anything with the query and i was thinking if this can also be acheived by substring/substring_index functions

Barmar Over a year ago

That's the normal datatype to store XML data in MySQL. I reproduced the failure at sqlfiddle, but I'm not sure why it fails.

Barmar Over a year ago

I don't have any experience using XML in MySQL.

Collectives™ on Stack Overflow

Select query from Text field with XML data in MySQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related