Re: Help needed with XMLTABLE

On Wed, Apr 15, 2020 at 12:56 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut(at)gmail(dot)com> napsal:
>>
>> One of our customers tried to use XMLTABLE syntax without
>> row_expression, which works fine with ORACLE but doesn't work with
>> PostgreSQL. So can anyone suggest what alternative we can use?
>>
>> CREATE TABLE user_pool_clean (
>> fk_user_pool_pk bytea NOT NULL,
>> user_id character varying(255) NOT NULL,
>> email_address character varying(250),
>> is_mil numeric,
>> is_civ numeric,
>> is_ctr numeric,
>> is_gov numeric,
>> is_edu numeric,
>> role_id character varying(50),
>> user_profile_id character varying(50),
>> service_branch_id character varying(50),
>> mil_pay_grade_id character varying(50),
>> my_auds character varying(4000),
>> my_orgs character varying(4000),
>> processed character(1) DEFAULT 'N'::bpchar NOT NULL
>> );
>>
>> insert into user_pool_clean
>> values('995CECDC1881375DE05312A270C7CF56','10015706','noemail(at)fter(dot)dbrefresh(dot)mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
>>
>> insert into user_pool_clean
>> values('995CECDC1905375DE05312A270C7CF56','10015848','noemail(at)fter(dot)dbrefresh(dot)mil',0,0,0,0,0,'1','3','700015','11','
>> 1705562,1708486','1710621','Y');
>>
>>
>> SQL> SELECT upc.is_mil,TRIM(column_value) src
>> FROM user_pool_clean upc
>> ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));
>
>
> It's very famous how people are creative - when xmltable has only one argument, then it is evaluated like XQuery expression.
>
> https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/
>
> Unfortunately, Postgres has not support of XQuery, so nothing similar is possible. Fortunately, Postgres has much better tools for parsing string.

Thanks for the help, Pavel.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com