Using PostgreSQL PgAdmin:
Let’s take a new start of using the XPath function in our queries of the PostgreSQL database. We have to start the POstgreSQL database GUI, i.e., PgAdmin using the Windows 10 search area. On your Windows 10 desktop, you have your search bar at the left bottom corner. Write “pgadmin” and tap enter. It will show you a list of matched applications. Tap on “PgAdmin” to launch it. It will take up to 20 to 30 seconds to launch. On opening, it will ask for your server database password. Without a server password, you can’t use it further. Therefore, you have to add the password and tap on the “OK” button on the appeared dialogue box. Now, your pgAdmin GUI is ready to use. Expand the left side “Server” option. You will find the databases listed in it. Expand the database of your choice, i.e., Postgres. We have been using the “aqsayasin” database right now. Tap on the query tool icon for the specific database to perform and execute the instructions. To use the “XPath” function, you must have a table containing an XML type column to store XML data. Therefore, we have been creating a new table, “Makeup” with the CREATE TABLE postgresql instruction on the query area. This table will contain only two columns ID and Info. The column “ID” is of integer type while the column “Info” is of “XML” type to store XML data in it. On running this query with the PgAdmin “run” button, the table has been created as per the output message displayed on the query tool are shown below.
Let’s search the whole records for the newly made table in our query area. Therefore, go to the table list within your database, i.e., “aqsayasin” in our case. You will find all your tables listed there. Right-click on the table “Makeup” and tap on the “View All rows” to fetch all records. The select instruction will be exected by pgAdmin itself, and the whole empty table will be displayed on the screen as below.
We need to insert some records in both of its columns using the INSERT INTO instruction on the query tool. Therefore, we used the INSERT INTO command to add the ID and XML data into the table “Makeup”. You can see that the XML data contains tags for different contents, i.e., food, item, price. You have to specify the ID for a specific tag so that you can fetch it in the future according to your choice. You can see that this first record contains the data for makeup for 2 items while the tags used within are the same for both, i.e., product, item, price. Add a total of 5 records, same as shown below.
After adding all the 5 records in the table “Makeup”, it is ready to display. We will display the whole table “Makeup” on our PostgreSQL pgAdmin screen using the SELECT instruction with the “*” sign below. The first column, “ID” contains an integer type value, while the “Info” column contains the XML data for the makeup brand and its items.
It’s time to make use of the XPath() function in our queries to fetch the XML data from the “Info” column of our table “Makeup” quickly. For that, you have to utilize the XPath function within the SELECT instruction of the PostgreSQL database. As we know, the XPath() function normally takes three arguments. But, we will be utilizing only two for this example here. Therefore, we have been utilizing the SELECT instruction to fetch all the records from the table “Makeup” while using the XPath() function within its WHERE clause. This function’s first argument is an XPath expression that lets us know about the node-set or tags in our XML data. You can say it is a“path” for XML values to be located. In our case, we have to locate the “item” node or tag from the XML data. The second argument is the actual data or XML column in which the XML data is residing. As we have a total of 2 same tags for “items”, it will search if the first tag “item” contains the item of name “Mascara” or not. If so, it will return that particular record and display it on our pgAdmin screen. You can see that the first tag “item” contains the record for the “Mascara” item in the column “Info”. This is how the XPath function works to search the particular data from the XML column in a table.
Let’s search for XML data from the same “Info” column of the Makeup table using the SELECT instruction and XPath function. Therefore, we have been using the same format of the “XPath” function in the SELECT query. This time, we have been searching the same data from the ID “2” of tag “item”. The output shows that the 2nd tag, “item” doesn’t contain such value and returns nothing.
Let’s fetch another XML record from the XML column “Info” of table “Makeup”. This time we are looking for the text “Primer” from the 2nd index of tag “item” in the column “Info”. In return, we have got it on the 5th row of the column “Info” as displayed in the image below.
Let’s use the XPath() function once again to fetch another record from the column “Info” of a table “Makeup”. This time, we will be fetching the record from the tag “price” of the column “Info” anywhere at any row. We have replaced the tag “item” with the tag “price” within the first argument of the XPath() function in the query. We have been using the condition to check whether the makeup product price equals 3000, the 2nd “price” tag anywhere in the “Info” column. The condition got matched as the 2nd “price” tag at the 4th record of column “Info”. The 4th row of column “Info” is displayed down in the image.
Conclusion:
Finally! We have discussed using the Xpath() function in the PostgreSQL database to manipulate, fetch and modify the PostgreSQL tables and columns. We have created a table with an XML column and added some user-defined tags in it with XML data. We have seen how easy it is to use the XPath() function within the SELECT instruction to fetch the specific XML text data using the tag path and the ID for a particular thing. We hope the concept of using XPath is no longer complicated for you, and you can use it anywhere anytime.
