When It comes to use XML features in PostgreSQL database DBA always face challanges as no clear steps available .
Recently while was working for one of our client thought of publishing it for My DBA friends.
Here is Very Higlevel steps what We can follow.
Error Statement:
mdi=# create table kmgtest (col1 xml)
CREATE TABLE
mdi=# insert into kmgtest values ('<one>something</one>');
ERROR: unsupported XML feature;
Because you have no XML library installed in your postgreSQL database.
Steps:Set youur enviromens right
postgres@servername:/home/postgres> $ export PATH=/opt/postgres/11/bin:/usr/local/sbin:/sbin:/bin:/usr/sbin:/usr/bin:/usr/X11R6/bin:/root/bin:/opt/postgres/11/bin:/opt/postgres/11/bin^C
postgres@servername:/home/postgres> $ export LD_LIBRARY_PATH=/opt/postgres/11/lib:/opt/postgres/oracle/instantclient_19_3/lib::/opt/postgres/11/lib:/usr/lib64/
postgres@servername:/home/postgres> $ /opt/postgres/softwares/postgresql-11.13/configure --prefix=/opt/postgres/11 --with-libxml --with-libxslt
shutdown db >Take backup for safer side but not neede.
Go to path wjhere your PostgreSQL SW reside else you can locate
locate configure
From command prompt:
--Prefix>Your PostgreSQL Home
--bindir >Your PostgreSQL executables
./configure --prefix=/opt/postgres/11 --bindir=/opt/postgres/10/bin/ --with-ldap --with-libxml --with-libxslt
xcess-precision=standard -O2
configure: using CPPFLAGS= -D_GNU_SOURCE -I/usr/include/libxml2
configure: using LDFLAGS= -Wl,--as-needed
preparing build tree... done
run make >It will take time
make install
You are all set to use XML features
SELECT xmlcomment('hello');
xmlcomment
--------------
<!--hello-->