XPATH Language Usage With An Example In PostgreSQL

In these days XML documents are everywhere. They are mainly used to exchange information between applications; however, they are also used for many other purposes. Moreover, almost all database engines supported by TeamSQL include XML as a data type. In this article we use TeamSQL to store XML documents on a database table, then we extract elements from the XML by using XPath expressions, and finally, we execute queries inside the XML document.


XML Example

XML documents are very flexible regarding format; they can have any tag names like the following example:



If we want to insert this XML document on a Postgres database, we can create a table called “family_xml” with an XML column and then insert the document as we can see in the following image:



If we like to extract the <father> element from the full XML document, we can execute the following XPath expression:



Let’s see how this XPath expression works in TeamSQL



Note in the previous image, the value returned by XPath is an XML document too, which is essential. If we want to return only the text value of the element, we need to add the text() function, as we can see in the following image:



Let’s add kids to our family, we insert a new XML with the <child> tag, and we query to obtain all child names. Let’s see it on the next print screen:



First of all, to clarify we changed the format of the XML document in the insert. We separate each tag in a different line and use tabs to indent the elements.

Now, focusing on the result of the XPath function. Can you see the difference with the father example we did previously? Now we have repetitive elements, do we have several child tags in the XML document. Then the xpath() function returns an array of elements. The enclosing ‘{}’ in the result means we have an array of elements, instead of one individual element. However, if we review the father example, we see we also had an array as a result. Yes, it’s true, in fact, the xpath() function ever returns an array (even when it has one single value)

Now is time to convert the array of the element in a table format, converting each element of the array into a record, like we have a table. To do this conversion, we use the unnest() function; Let’s do it:



As you can see, in the previous result, they are not enclosing ‘{}’



XML Element Attributes

In XML it is possible to have attributes to describe an element. Let’s suppose we want to add the age to every family member element. We can insert the following XML document to include the age as an attribute.



We can query the attribute value by using the following XPath expression. Note the ‘@’ before the attribute name.



Finally, if we want to extract only one element based on the value of some of its attributes, we can add a condition enclosed in square brackets []. In the following image, we can see an example where we extract the child element with age value = “13”.


Exercise for the reader

With TeamSQL use the following insert to populate the table and do the exercises described below:



  • Using XPath() obtain a list with the names of all mothers.
  • Using XPath() obtain a list with all families with all its members in XML format.
  • Using XPath() obtain a list with the names of all the members of family Jensen.



Xpath() is a flexible function, to manipulate XML documents. In this article we did several operations on an XML document stored in a table using TeamSQL like the insertion of an XML, extract of elements from an XML and query some elements based on a condition. In a next article, we continue working with the next step in XML processing: The XQuery language, we do operations with XQuery over XML using TeamSQL. Let’s do it together!