sql - PostgreSQL:将 ms sql xml 查询迁移到 postgresql 查询

Question

我正在尝试将以下 MS SQL Server 过程迁移到 PostgreSQL 函数。

    CREATE PROCEDURE [dbo].[GMC]

    AS
    BEGIN

    DECLARE @LID VARCHAR(3);
    DECLARE @xml XML = '<XMLProf><CID>840</CID><MD>101113</MD></XMLProf>';

    SELECT  @LID = Pay.b.value('.','Varchar(3)')
    FROM    @xml.nodes('/XMLProf/CID') as Pay(b)

    SELECT  'Return Value' = @LID

    END

我已经尝试转换为以下内容，但它不起作用。

    CREATE OR REPLACE FUNCTION dbo.GMC()
    RETURNS void
    AS
    $BODY$
    DECLARE 
            LID VARCHAR(3);
            bxml XML = '<XMLProf><CID>840</CID><MD>101113</MD></XMLProf>';

            BEGIN

            SELECT  LID = Pay.b.value('.','Varchar(3)')
            FROM    XMLTABLE('/XMLProf/CID' PASSING bxml) as Pay(b)

            SELECT  'Return Value' = LID
    end;
    $BODY$
    LANGUAGE  plpgsql;

编辑:我期待的结果是“840”

我收到的错误是语法错误:

ERROR:  syntax error at or near ")"
LINE 12:     FROM XMLTABLE('/XMLProf/CID' PASSING bxml) as Pay(b)

谁能告诉我如何完成这个。非常感谢任何帮助。

Answer 1

如果你想从一个函数返回一些东西，你不能使用returns void。由于 XML 是字符数据，返回文本 更有意义。

因为您只想返回一个值，所以实际上不需要 xmltable()。而且您也不需要 PL/pgSQL:

CREATE OR REPLACE FUNCTION dbo.gmc()
  RETURNS text
AS
$BODY$
  select (xpath('/XMLProf/CID/text()', 
                '<XMLProf><CID>840</CID><MD>101113</MD></XMLProf>'::xml))[1]::text;
$BODY$
LANGUAGE sql;

xpath() 返回所有匹配项的数组，这就是为什么需要 [1] 来挑选第一个匹配项。

假设您确实想将 XML 传递给函数，您可以使用:

CREATE OR REPLACE FUNCTION dbo.gmc(p_xml xml)
  RETURNS text
AS
$BODY$
  select (xpath('/XMLProf/CID/text()', p_xml))[1]::text;
$BODY$
LANGUAGE sql;