PHP MySQL How to fetch xml value

Question

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!

Look into SimpleXML

Patrick Q
– Patrick Q

2018-03-22 17:46:23 +00:00
Commented Mar 22, 2018 at 17:46 — Patrick Q
– Patrick Q, Commented Mar 22, 2018 at 17:46
SimpleXML is anything but simple, try DomDocument instead.

Sammitch
– Sammitch

2018-03-22 18:05:28 +00:00
Commented Mar 22, 2018 at 18:05 — Sammitch
– Sammitch, Commented Mar 22, 2018 at 18:05

Flo · Accepted Answer · 2018-03-22 18:05:15Z

2

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)

answered Mar 22, 2018 at 18:05

Flo

5193 silver badges10 bronze badges

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

1 Comment

mdsmith98 Over a year ago

I came up with this: $blob_query2 = mysqli_query($dbh8, "SELECT ExtractValue(data, '//void/string') as EXTRACTED_DATA from objstore WHERE obj_id='$characterId' AND type='PLAYER' AND namespace_int='3' ORDER BY obj_id DESC LIMIT 1"); $count = mysqli_num_rows($blob_query2); $blob_row2 = mysqli_fetch_assoc($blob_query2); $data2 = $blob_row2['data']; echo "Data: $data2"; However, it still doesn't give me "Rachels" Also, is there a way to take the property="name" into account, so it will fetch that piece of the XML? The XML is actually very large, I only showed an example.

Collectives™ on Stack Overflow

PHP MySQL How to fetch xml value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related