PL/SQL

February 24, 2007

CSV to XMLTYPE to TABLE

Filed under: example, EXTRACTVALUE, pl/sql code, XMLSEQUENCE, XMLTYPE — Michael Moore @ 10:22 pm

Take a CSV string and use it as SELECT criteria.

DECLARE
mystr             VARCHAR2 ( 2000 );
mycomma_list      VARCHAR2 ( 1000 ) := '1000,999,0980,49494';

BEGIN
mystr := '<list><x>' || REPLACE ( mycomma_list, ',', '</x><x>' ) || '</x></list>';
 
FOR cur2 IN
  ( SELECT portal_name
  FROM tx_portal
  WHERE portal_key IN (
        SELECT TO_NUMBER ( EXTRACTVALUE ( COLUMN_VALUE, '/x' ) )
        FROM TABLE ( SELECT XMLSEQUENCE ( EXTRACT ( XMLTYPE.createxml ( mystr )
        , '/list//x' ) )
       FROM DUAL ) ) )
LOOP
DBMS_OUTPUT.put_line ( 'Got ' || cur2.portal_name );
END LOOP;
END;

Advertisements

1 Comment »

  1. Hello.

    This code is impressive.

    One question, as are returned more than one column per row?

    Thank you

    Comment by Poliburro — April 30, 2008 @ 3:18 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: