CODEWITHSUNDEEP
sqlxmlpostgresql
User12111111
User12111111

Reputation: 1219

PostgreSQL: migrating ms sql xml query to postgresql query

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.

Upvotes: 2

Views: 668

Answers (2)

Jim Jones
Jim Jones

Reputation: 19653

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.

Upvotes: 1

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions