How to use JSON and XML data from Oracle database in PHP

How to use JSON and XML data of Oracle database in PHP

Introduction:
In the process of web development, we often need to obtain and store data from the database. In addition to traditional relational data, such as text, numbers, and dates, more and more applications now need to process unstructured data, such as JSON and XML. Oracle is a popular relational database system that provides powerful capabilities to handle this unstructured data. In this article, we will learn how to use JSON and XML data from Oracle database in PHP and provide code examples.

1. Preparation
Before starting, we need to ensure the following points:

1. Install and configure the Oracle database.
2. Install PHP and related Oracle database drivers. You can use PECL or manual installation.
3. Create an Oracle database table containing columns of JSON and XML types. The following is a simple example table structure:

CREATE TABLE my_table (

id NUMBER PRIMARY KEY,
json_data CLOB,
xml_data XMLTYPE

);

2. Use PHP to operate JSON data

1. Connect to Oracle database:

$conn = oci_connect('username', 'password', 'localhost/orcl');
if (! $conn) {

$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);

}

2. Insert JSON data:

$json_data = '{"name": "John", "age": 30, "city": "New York"}';
$stmt = oci_parse($conn, 'INSERT INTO my_table (id, json_data) VALUES (1, :json_data)') ;
oci_bind_by_name($stmt, ':json_data', $json_data);
oci_execute($stmt);
oci_commit($conn);

3. Query JSON data:

$stmt = oci_parse($conn, 'SELECT json_data FROM my_table WHERE id = 1');
oci_execute($stmt);
$json_data = oci_fetch_assoc($stmt)['JSON_DATA'];
$data = json_decode($json_data, true);
echo $data['name']; // Output John

3. Use PHP operates XML data

1. Insert XML data:

$xml_data = <<

<person>
    <name>John</name>
    <age>30</age>
    <city>New York</city>
</person>

XML;

$stmt = oci_parse($conn, 'INSERT INTO my_table (id, xml_data) VALUES (2, XMLTYPE(:xml_data)) ');
oci_bind_by_name($stmt, ':xml_data', $xml_data);
oci_execute($stmt);
oci_commit($conn);

2. Query XML data ：

$stmt = oci_parse($conn, 'SELECT xml_data.getClobVal() AS xml_data FROM my_table WHERE id = 2');
oci_execute($stmt );
$xml_data = oci_fetch_assoc($stmt)['XML_DATA'];
$xml = simplexml_load_string($xml_data);
echo $xml->person->name; // Output John

Conclusion:
This article introduces how to use JSON and XML data of Oracle database in PHP. First, we need to prepare the database environment and create tables containing JSON and XML type columns. We can then use PHP's Oracle extension to connect to the database and perform insert and query operations. Through sample code, we show how to manipulate JSON and XML data. I hope this article will help you use Oracle database to process JSON and XML data in PHP.

The above is the detailed content of How to use JSON and XML data from Oracle database in PHP. For more information, please follow other related articles on the PHP Chinese website!