1

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="testadmin@abc.com"/>
      <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.

sqllearner
  • 105
  • 1
  • 7

1 Answers1

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

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • 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 – sqllearner Nov 03 '20 at 20:30
  • 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 Nov 03 '20 at 21:04
  • I don't have any experience using XML in MySQL. – Barmar Nov 03 '20 at 21:06