Converting Enterprisedb Postgres Plus XML Array To Array

In Postgres Plus Advanced Server 8.3R2, there are times when you want to query a table using values passed in as XML.  Here’s an example. First we’ll create a table and insert a few values:


CREATE TABLE NAMES (FNAME VARCHAR2(50));

Table created.

INSERT INTO NAMES VALUES ('MIKE');

1 row INSERTED.

INSERT INTO NAMES VALUES ('STEVE');

1 row INSERTED.

INSERT INTO NAMES VALUES ('BILL');

1 row INSERTED.

COMMIT;

COMMIT completed.


Next, we’ll query our table to check our values:

SELECT * FROM NAMES;

FNAME
--------------------------------------------------
MIKE
STEVE
BILL


Finally, we’ll try to use XPATH and parse some values from XML and use them in a query to query our table:


SELECT FNAME FROM NAMES WHERE FNAME = ANY(XPATH('//FNAME/TEXT()','<TESTXML><FNAME>JAMES</FNAME><FNAME>BILL</FNAME></TESTXML>'));


ERROR near line 1:
operator does not exist: character varying = "xml"
Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts.
Position: 37

What is the problem? The problem is that XPATH returns an xml array and the ANY clause expects a standard array. If you try casting the XPATH results to an array, it won’t work either:


SELECT FNAME FROM NAMES WHERE FNAME = ANY((XPATH('//FNAME/TEXT()','JAMESBILL'))::text[]);
ERROR near line 1:
cannot cast type "xml"[] to text[]
Position: 131

So what is the solution? You need a custom function to convert an XML array into a TEXT array:


CREATE OR REPLACE FUNCTION xml_array_to_array( pxml xml[]) RETURNS text[] AS
$$
DECLARE

myvalues varchar2;
myarray text[];
BEGIN

-- Just assign the pxml array to a varchar2

myvalues := pxml;

Now, let’s query the table using our new function:

SELECT FNAME FROM NAMES WHERE FNAME = ANY( xml_array_to_array((XPATH('//FNAME/text()','JAMESBILL')) ));

FNAME
--------------------------------------------------
BILL