CODEWITHSUNDEEP
phpmysqlxml
mdsmith98
mdsmith98

Reputation: 59

PHP MySQL How to fetch xml value

I am trying to fetch an XML value from a database, but do not know where to even begin. Basically, I have some XML in a mysql field type of text, and the value I am trying to fetch is "Rachels" as shown here:

<void property="name">
   <string>Rachels</string>
</void>

I am using mysqli procedural, so would like the code in that format.

My code so far looks like this. This code fetches the whole field, not just that one value I want it to fetch.

$blob_query = mysqli_query($dbh8, "SELECT * FROM objstore WHERE obj_id='$characterId' AND type='PLAYER' AND namespace_int='3' ORDER BY obj_id DESC LIMIT 1");
        if($blob_query){
            $blob_row = mysqli_fetch_assoc($blob_query);
            echo $blob_row['data'];
        }

So I need to extract the value from that XML. Thanks for the help in advance!

Upvotes: 0

Views: 182

Answers (1)

Flo
Flo

Reputation: 499

Have a look at the ExtractValue MySQL function : https://dev.mysql.com/doc/refman/5.7/en/xml-functions.html#function_extractvalue

It lets you extract values from XML content, using XPath. Which, from your example, gives the following:

mysql> select * from objstore\G
*************************** 1. row ***************************
data: <void property="name">
   <string>Rachels</string>
</void>
1 row in set (0,00 sec)

mysql> SELECT ExtractValue(data, '//void/string') as EXTRACTED_DATA FROM objstore;
+----------------+
| EXTRACTED_DATA |
+----------------+
| Rachels        |
+----------------+
1 row in set (0,00 sec)

Upvotes: 2

Related Questions