Not able to read values from xml in postgres-CodePudding

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. Any leads will be helpful. Thanks

CodePudding user response：

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:

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'

Note that creating the temporary table in the function is completely unnecessary, you can return the result of xmltable() directly

CREATE OR REPLACE FUNCTION xml()
    RETURNS TABLE(name character varying, description character varying, parentpid character varying, level integer, nodenumber character varying, displayorder integer, iscustom boolean) 
    LANGUAGE plpgsql
    ROWS 1
AS $BODY$
declare
  xmlvalue xml := 
    '<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
  return query
    SELECT d.name,d.description,d.parentpid,d.level,d.nodenumber,d.displayorder,d.iscustom 
    FROM XMLTABLE('/ArrayOfClientProcess/ClientProcess' 
                  PASSING xmlvalue
                  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; 
end;
$BODY$;

In fact, you wouldn't even need PL/pgSQL for this. A simple language sql would do as well.