無法從postgres中的xml讀取值

我正在嘗試從 xml 中讀取并將它們插入到一個臨時表中并回傳該表作為結果。下面是我正在嘗試使用的 Postgres 函式

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$;

當我執行該函式時，臨時表將 null 顯示為列值。讓我覺得它可能無法正確讀取 xml 中的值。我在決議時可能做錯了什么。任何線索都會有所幫助。謝謝

uj5u.com熱心網友回復：

假設無效LastModifiedOn>是復制和粘貼錯誤，則錯誤在列的 XPATH 運算式中。標簽名稱區分大小寫，因此您需要此列定義：

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' 

注意在函式中創建臨時表是完全沒有必要的，可以xmltable()直接回傳結果

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$;

事實上，你甚至不需要為此使用 PL/pgSQL。一個簡單language sql的也可以。

標籤：PostgreSQL plpgsql xml表

上一篇：如果提示為空，如何停止js腳本

下一篇：postgresql將2個不同日期的查詢合并到1個表中