PostgreSQL: migrating ms sql xml query to postgresql query

Question

I am trying to migrate the following MS SQL Server procedure to a PostgreSQL function.

    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

I have tried to convert to the following but it doesn't work.

    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;

Edit: The result I am expecting is "840"

The error that I am getting is a syntax error:

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

Can someone please tell me how can I accomplish this. Any help is really appreciated.

Can you also add what is the result you're expecting and the error messages you might be getting? — Jim Jones
– Jim Jones, Commented Nov 18, 2019 at 13:14
You can't return something from a function that's defined as returns void — user330315
– user330315, Commented Nov 18, 2019 at 13:24

user330315 · Accepted Answer · 2019-11-18 13:29:50Z

2

If you want to return something from a function, you can't use returns void. As XML is character data, returns text makes more sense.

As you only want to return a single value, xmltable() isn't really needed. And you don't need PL/pgSQL either:

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() returns an array of all matches, that's why the [1] is needed to pick out the first match.

Assuming you actually want to pass the XML to the function, you can use this:

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;

answered Nov 18, 2019 at 13:29

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Evgeny Nozdrev Over a year ago

You can use XMLTABLE in Postgres 10+.

Jim Jones · Accepted Answer · 2019-11-18 13:50:23Z

1

If you're basing your response element on the string size of the inner nodes of XMLProf, you might wanna take a look at XPATH and UNNEST.

CREATE OR REPLACE FUNCTION gmc() RETURNS text
AS $BODY$
WITH j AS (
 SELECT 
   UNNEST(XPATH('//XMLProf/node()',
                '<XMLProf><CID>840</CID><MD>101113</MD></XMLProf>'::XML)) AS rawxml
) SELECT (XPATH('//text()',j.rawxml))[1]::TEXT FROM j
  WHERE CHAR_LENGTH((XPATH('//text()',j.rawxml))[1]::TEXT) = 3
$BODY$
LANGUAGE sql;

Testing ..

db=# SELECT * FROM gmc();
 gmc 
-----
 840
(1 Zeile)

If you know exactly where to look and the string length is irrelevant, just get rid of the UNNEST and use the XPATH /XMLProf/CID/text() as pointed out by @a_horse_with_no_name.

edited Nov 18, 2019 at 13:50

answered Nov 18, 2019 at 13:39

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: migrating ms sql xml query to postgresql query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related