SELECT getXMLDocument('gpxtestrevised.gpx') LANGUAGE ' sql ' VOLATILE SECURITY DEFINER ĪLTER FUNCTION getxmldocument ( character varying ) OWNER TO postgres Ĭopy the gpxtestrevised.gpx file into the gpxdir and call the below we set the end read to some big number - because we are too lazy to grab the length - and it will cut of at the EOF anyway SELECT CAST ( pg_read_file (E ' gpxdir/ ' | | $ 1, 0, 100000000 ) As xml ) create the function to load xml doc CREATE OR REPLACE FUNCTION getXMLDocument (p_filename character varying ) RETURNS xml AS Only super users can use the pg_read_file, so to allow regular users access to this, we have this run in the postgres context and then can give rights to this function to those users we want to who may not have super user rights. Please note - our getXMLDocument function is marked as SECURITY DEFINER because Now we'll create a function to mirror Simon's getClobDocument, except instead of calling it getClobDocument, we'll call it getXMLDocument because it will return an XML object instead of a CLOB object. You can determine the location of your cluster by running as super user Perl or python or some other language such as PLPerlU that has system file access.įor now we'll just create a folder called gpxdir in the PostgreSQL cluster. There is also the pg_read_file which does what we want, but can only read files from the PostgreSQL init cluster. Parallel in PostgreSQL, I would say its the lo_* functions that allow import export of files into the db, though that only allows you to import and export files and not read the file. Simon used Oracle's get LOB fileopen to get the xml file into the db which he calls from an Oracle stored function. Simon had this as the first part Īnd we changed it to: Getting the data in the database Our revision of Simon's revision can be downloaded from here
We will be using the xpath version that takes no schema references. Suffice it to say, our version is like Simon's revised version except we also stripped off the namespace references since the PostgreSQL XML parser seemed unhappy that the name space defined was never referenced in format xsi. SELECT xpath ( ' /my:a/text() ', ' test ', Also note that the xpath function always returns an array even if there is only one element. Where the second argument is an array of 2 dimensionalĪrrays with the first item being the schema name and second the URI for the schema. The GPX example Simon had a location but no name. To require that the schema have a name in addition to a location or at least that is what we concluded. We will use the same sample data Simon used, except sadly we had to change it further because the schema wasn't defined in such a way that PostgreSQL liked or rather I was too stupid to construct the XPath statement in such a fashion that would satisfy the You can also refer to the PostgreSQL XML section of the documentation. The following is a quick primer on XPath that seems useful. XPath is a language used to query XML data and PostgreSQL supports the XPathġ.0 version. The key function we will use is the xpath function. In this example we shall demonstrate the built in functionality in 8.3 and above.
Before 8.3, you could use the xml2 contrib module to achieve the same effect in a not so standards compliant sort of way. PostgreSQL since 8.3 has had ANSI SQL 2003 XML functionality built in.