I am trying to read from a xml and insert them in a temporary table and return that table as a result. Below is the Postgres function I am trying with
CREATE OR REPLACE FUNCTION public.xml(
)
RETURNS TABLE(name character varying, description character varying, parentpid character varying, level integer, nodenumber character varying, displayorder integer, iscustom boolean)
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
ROWS 1000
AS $BODY$
declare
xmlvalue text := '<ArrayOfClientProcess>
<ClientProcess><Id>0</Id><IsActive>false</IsActive><LastModifiedBy>email</LastModifiedBy>
<LastModifiedOn>2022-04-27</LastModifiedOn><ClientId>0</ClientId><ProcessId>0</ProcessId>
<IsUserSelected>false</IsUserSelected><IndustryId>0</IndustryId><Occurrence>false</Occurrence><ParentPId>NULL</ParentPId>
<CAMSAssigned>false</CAMSAssigned><KPIAssigned>false</KPIAssigned><IsTechVariantAvailable>false</IsTechVariantAvailable>
<IBSCentralId>0</IBSCentralId><MasterId>0</MasterId><MasterCentralId>0</MasterCentralId><SegmentId>0</SegmentId><CAMId>0</CAMId>
<IsEPCAvailable>false</IsEPCAvailable><IsVariantAvailable>false</IsVariantAvailable><IsSubvariantAvailable>false</IsSubvariantAvailable>
<LOMVariantId>0</LOMVariantId><ProcessLevel>0</ProcessLevel><OperationsPresence>false</OperationsPresence><IsDigitalImpact>false</IsDigitalImpact>
<IsCrossIndustry>false</IsCrossIndustry><Name>FinanceCustom</Name><Description>Finance</Description><ParentId>0</ParentId><Level>0</Level>
<NodeNumber>0</NodeNumber><GeographyId>0</GeographyId><DisplayOrder>0</DisplayOrder><IsCustom>true</IsCustom><IsCustomMapped>false</IsCustomMapped>
<IsProcessChecked>false</IsProcessChecked><islinkedwithCPA>0</islinkedwithCPA><ProcessIdfromCPA>0</ProcessIdfromCPA>
<IsProcessCheckedOnLeftPane>false</IsProcessCheckedOnLeftPane></ClientProcess></ArrayOfClientProcess>';
begin
CREATE TEMPORARY TABLE tempClientProcess(name character varying,
description character varying,
parentpid character varying,
level integer,
nodenumber character varying,
displayorder integer,
iscustom boolean);
with data as (
select xmlvalue::xml val)
INSERT INTO tempClientProcess(name,description,parentpid,level,nodenumber,displayorder,iscustom)
SELECT d.name,d.description,d.parentpid,d.level,d.nodenumber,d.displayorder,d.iscustom
FROM data x,
XMLTABLE('/ArrayOfClientProcess/ClientProcess'
PASSING val
COLUMNS
name character varying PATH 'name',
description character varying PATH 'description',
parentpid character varying PATH 'parentpid',
level integer PATH 'level',
nodenumber character varying PATH 'nodenumber',
displayorder integer PATH 'displayorder',
iscustom boolean PATH 'iscustom') as d;
return query
select * from tempClientProcess ;
drop table tempClientProcess;
end;
$BODY$;
When i execute the function, the temporary table displays null as column values. Makes me think that it might not be able to read the values from xml properly. Something wrong I am doing while parsing maybe.
Assuming the invalid
LastModifiedOn>
is a copy & paste error, the error is in your XPATH expression for the columns. Tag names are case sensitive, so you need this column definition:Note that creating the temporary table in the function is completely unnecessary, you can return the result of
xmltable()
directlyIn fact, you wouldn't even need PL/pgSQL for this. A simple
language sql
would do as well.