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;

-- Strip off the brackets

myvalues := replace(myvalues,'{','');
myvalues := replace(myvalues,'}','');

-- Convert the varchar string into an array

myarray := string_to_array(myvalues,',');

-- Return the array
RETURN myarray;

END;
$$ LANGUAGE PLPGSQL;
/

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

No Comments

Start the ball rolling by posting a comment on this article!

Leave a Reply




XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>