[SOLVED] Insert values in temporary table from xml using Xmltable postgres

Insert values in temporary table from xml using Xmltable postgres

I am trying to pass xml nodes as a parameter and extract values from them and insert them into a table. I am able to do so if I pass the entire xml node but if I try to pass it through a variable I am facing syntax error. Since I will be having dynamic xml which will need to be parsed , I have to pass it via variable only. I am providing a simplified version below of what I am trying to achieve. I am getting a syntax error 'syntax error at or near "xmlvalue"'

CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
with data as ( 
select xmlvalue::xml val)
--select '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>'::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )   
; 
select * from sample

Edit 1 : As suggested I am now trying to wrap the above code inside a function, since we can't use variables outside procedures/functions.

create or replace function xml()
returns table(Id text,Author Text,Title Text)
as $$
declare
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
begin
CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);
with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )   
; 
return query
select s.Id,s.Author,s.Title from sample s ;
end;
$$
language plpgsql

While trying to execute the above function I am getting the below errors. What I understand from the error is I have to provide the table alias name to refer to a column.

ERROR:  column reference "id" is ambiguous
LINE 4: SELECT Id, Author,Title 
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)
SELECT Id, Author,Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' )
CONTEXT:  PL/pgSQL function xml() line 6 at SQL statement
SQL state: 42702

Solution

This works.

create or replace function xml()
returns table(Id text,Author Text,Title Text)
as $$
declare
xmlvalue text := '<Book><Id>1</Id><Author>Subhrendu</Author><Title>Postgre</Title></Book>';
begin
CREATE TEMPORARY TABLE SAMPLE(Id text,Author text,Title text);

with data as ( 
select xmlvalue::xml val)
INSERT INTO SAMPLE(Id, Author,Title)

SELECT d.Id,d.Author,d.Title 
FROM   data x, 
XMLTABLE('/Book' 
PASSING val 
COLUMNS  
Id    text  PATH 'Id', 
Author text  PATH 'Author', 
Title text PATH 'Title' ) as d
; 
return query
select s.Id,s.Author,s.Title from sample s ;
end;
$$
language plpgsql