Thread: Help needed with XMLTABLE
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@fter.dbrefresh.mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
insert into user_pool_clean
values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@fter.dbrefresh.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, ',', '","')|| '"'));
IS_MIL SRC
---------- ---------------
0 1705562 --------O/P from the oracle database
0 1708486
0 1706882
postgres[7604]=# SELECT upc.is_mil,TRIM(column_value) src
postgres-# FROM user_pool_clean upc
postgres-# ,xmltable(('"'|| REPLACE( upc.my_auds, ',',
'","')|| '"'));
ERROR: syntax error at or near ")"
LINE 3: ... ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut@gmail.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@fter.dbrefresh.mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
insert into user_pool_clean
values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@fter.dbrefresh.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, ',', '","')|| '"'));
IS_MIL SRC
---------- ---------------
0 1705562 --------O/P from the oracle database
0 1708486
0 1706882
postgres[7604]=# SELECT upc.is_mil,TRIM(column_value) src
postgres-# FROM user_pool_clean upc
postgres-# ,xmltable(('"'|| REPLACE( upc.my_auds, ',',
'","')|| '"'));
ERROR: syntax error at or near ")"
LINE 3: ... ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));
this example is very classic - I newer see ugly code and dirty data elsewhere than on Oracle
SELECT upc.is_mil,
TRIM(column_value) src
FROM user_pool_clean upc,
FROM user_pool_clean upc,
unnest(string_to_array(replace(my_auds,e'\n',''), ',')) column_value;
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
SELECT upc.is_mil,
TRIM(column_value) src
FROM user_pool_clean upc,
FROM user_pool_clean upc,
regexp_split_to_table(replace(my_auds,e'\n',''), ',') column_value;
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
┌────────┬─────────┐
│ is_mil │ src │
╞════════╪═════════╡
│ 0 │ 1706882 │
│ 0 │ 1705562 │
│ 0 │ 1708486 │
└────────┴─────────┘
(3 rows)
Regards
Pavel
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut@gmail.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@fter.dbrefresh.mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
insert into user_pool_clean
values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@fter.dbrefresh.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.
Unfortunately, Postgres has not support of XQuery, so nothing similar is possible. Fortunately, Postgres has much better tools for parsing string.
Regards
Pavel
IS_MIL SRC
---------- ---------------
0 1705562 --------O/P from the oracle database
0 1708486
0 1706882
postgres[7604]=# SELECT upc.is_mil,TRIM(column_value) src
postgres-# FROM user_pool_clean upc
postgres-# ,xmltable(('"'|| REPLACE( upc.my_auds, ',',
'","')|| '"'));
ERROR: syntax error at or near ")"
LINE 3: ... ,xmltable(('"'|| REPLACE( upc.my_auds, ',', '","')|| '"'));
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
On Wed, Apr 15, 2020 at 12:56 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
> st 15. 4. 2020 v 7:32 odesílatel Dilip Kumar <dilipbalaut@gmail.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@fter.dbrefresh.mil',0,0,0,0,0,'1','4','700006','3','1706882','1707720','Y');
>>
>> insert into user_pool_clean
>> values('995CECDC1905375DE05312A270C7CF56','10015848','noemail@fter.dbrefresh.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
bettertools for parsing string.
Thanks for the help, Pavel.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com