You may have worked with HTML, XML and CSS while studying web design. XML is considered an extensible markup language that contains user-defined tags without the tags already defined, as in “Html”. When working with a PostgreSQL database, there are times when you need to store XML type data in your tables. Most students get confused when extracting this kind of data from PostgreSQL database tables. Therefore, the PostgreSQL database provides us with the “XPath” function for retrieving and modifying columns of XML type data. Thus, this article will contain an explanation of the use of the Xpath function in a PostgreSQL database. Let’s get started and try not to skip a single step.
Using PostgreSQL PgAdmin
Let’s start using the XPath function in our PostgreSQL database queries in a new way. We have to launch the POstgreSQL database GUI i.e. PgAdmin using the Windows 10 search area. On the Windows 10 desktop you have a search bar on the bottom left corner. Type “pgadmin” and press Enter. It will show you a list of related apps. Click on “PgAdmin” to launch it. It will take 20 to 30 seconds to start up. When opened, it will ask for your server database password. Without the server password, you will not be able to use it further. Therefore, you need to add a password and click the “OK” button in the dialog box that appears. Your pgAdmin GUI is now ready to use. Expand the “Server” option on the left. You will find the databases listed in it. Extend the database of your choice, such as Postgres. We are currently using the “aqsayasin” database. Click on the query tool icon for a specific database to follow and complete the instructions. To use the “XPath” feature, you must have a table containing an XML type column to store the XML data. Therefore, we created a new table “Makeup” using the CREATE TABLE postgresql statement in the query scope. This table will contain only two columns ID and Info.
The ‘ID’ column is of integer type and the ‘Info’ column is of ‘XML’ type to store XML data in it. When executing this query using PgAdmin’s Run button, the table was created according to the output message displayed in the query tool as shown below. “Makeover” with CREATE TABLE postgresql statement in query scope. This table will contain only two columns ID and Info. The ‘ID’ column is of integer type and the ‘Info’ column is of ‘XML’ type to store XML data in it. When executing this query using PgAdmin’s Run button, the table was created according to the output message displayed in the query tool as shown below. “Makeover” with CREATE TABLE postgresql statement in query scope. This table will contain only two columns ID and Info. The ‘ID’ column is of integer type and the ‘Info’ column is of ‘XML’ type to store XML data in it. When executing this query using PgAdmin’s Run button, the table was created according to the output message displayed in the query tool as shown below.
Let’s search for all records for the newly created table in our query area. So go to the list of tables in your database i.e. in our case “aqsayasin”. There you will find all your tables. Right click on the “Makeup” table and click on “View All Rows” to get all records. The select statement will be executed by pgAdmin itself and the entire empty table will be displayed on the screen as shown below.
We need to insert some records in both of its columns using the INSERT INTO statement in the query tool. So we used the INSERT INTO command to add the ID and XML data to the Makeup table. You can see that the XML data contains tags for various content i.e. food, item, price. You must provide an ID for a specific tag so that you can retrieve it in the future according to your choice. You can see that this first entry contains the composition data for 2 items, while the tags used internally are the same for both, i.e. product, article, price. Add a total of 5 entries as shown below.
Once all 5 entries have been added to the Makeup table, it is ready to be displayed. We will display the entire “Makeup” table in our PostgreSQL pgAdmin screen using the SELECT statement with the “*” sign below. The first “ID” column contains an integer value, and the “Info” column contains XML data for the cosmetics brand and its products.
It’s time to use the XPath() function in our queries to quickly get the XML data from the Info column of our Makeup table. To do this, you must use the XPath function in a PostgreSQL database SELECT statement. As we know, the XPath() function usually takes three arguments. But we will only use two for this example here. Therefore, we used the SELECT statement to retrieve all records from the “Makeup” table when using the XPath() function in its WHERE clause. The first argument to this function is an XPath expression that lets us know about a set of nodes or tags in our XML data. We can say that this is a “path” for the location of XML values. In our case, we need to find the node or tag “element” from the XML data. The second argument is the actual data, or the XML column that contains the XML data. Since we have a total of 2 identical ‘item’ tags, it will look to see if the first ‘item’ tag contains an item called ‘mascara’ or not. If so, it will return that particular entry and display it on our pgAdmin screen. You can see that the first “item” tag contains an entry for the position “Mascara” in the “Info” column. This is how the XPath function works to find specific data from an XML column in a table.
Let’s search for XML data from the same “Info” column of the Makeup table using the SELECT statement and the XPath function. Therefore, we used the same “XPath” function format in the SELECT query. This time we were looking for the same data by the id “2” of the “item” tag. The output shows that the 2nd “item” tag does not contain such a value and returns nothing.
Let’s take one more XML record from the “Info” XML column of the “Makeup” table. This time we are looking for the text “Primer” from the 2nd index of the “item” tag in the “Info” column. Instead, we got it in the 5th row of the “Info” column, as shown in the image below.
Let’s use the XPath() function again to get another record from the “Info” column of the “Makeup” table. This time, we’ll extract the record from the “price” tag of the “Info” column anywhere on any row. We have replaced the “item” tag with the “price” tag in the first argument to the XPath() function in the query. We used a condition to check if the price of cosmetics product is 3000, the 2nd price tag anywhere in the Info column. The condition matched on the 2nd “price” tag in the 4th record of the “Information” column. At the bottom of the image, the 4th row of the “Information” column is displayed.
Finally! We have discussed using the Xpath() function in a PostgreSQL database to manipulate, select, and modify PostgreSQL tables and columns. We have created a table with an XML column and added some custom XML data tags to it. We’ve seen how easy it is to use the XPath() function in a SELECT statement to retrieve specific XML text data using a tag path and an identifier for a particular thing. We hope that the concept of using XPath no longer seems complicated to you, and that you can use it anywhere, anytime.