There are other built-in functions that assist in getting essential data from an XML document, in addition to XPath and XPath String.
Despite the fact that each of these functions yields a different outcome, they all work in the same way. Everything is based on the information in the XML document. Although no XML validation is done, incorrect xml generates an error.
The following is a list of functions from the language manual:
xpath_booleanreturns a boolean. It returns true if the XPath expression evaluates to true, or if a matching node is found.
xpath_shortreturns a short integer.
xpath_intreturns an integer.
xpath_longreturns a long integer.
xpath_floatreturns a floating point number.
xpath_double,xpath_numberreturns a double-precision floating point number (
xpath_numberis an alias for
Here is my data for this exercise:
—Creating the table
CREATE TABLE xmltest2(line String);
—Loading the data into the table.
LOAD DATA LOCAL INPATH ‘Desktop/Docs/xmltest2.csv’ INTO TABLE xmltest2;
—Here is all UDFs in a single query
xpath_short(line, ‘/ProductInfo/ProductID/text()’) as ProductID,
xpath_int(line, ‘/ProductInfo/BranchID/text()’) as BranchID,
xpath_long(line, ‘/ProductInfo/UniqueID/text()’) as UniqueID,
xpath_float(line, ‘/ProductInfo/Price/text()’) as Price,
xpath_double(line, ‘/ProductInfo/TotalSales/text()’) as TotalSales,
xpath_boolean(line, ‘/ProductInfo/UniqueID/text()’) as UniqueIDExists
The below is the outcome of the query:
ProductID BranchID UniqueID Price TotalSales UniqueIDExists
100 1147483647 971305374745 200.5 2405228.59 true
101 1847483647 971305374746 475.5 4941198.59 true
102 1947483647 0 882.0 9114101.59 false
Hope you find this article helpful.
Please subscribe to get updates on latest posts.