How to read data from xml in postgresql function?

Question

how to read the xml data in postgresql stored procedure.and how to send data through xml and call stored procedure in java.

i can read the xml data through sqlserver but same thing i use in postgres stored procedure like

insert into temp_StudentDetails (studentId, studentFirstName, studentMiddleName, studentLastName, dateOfBirth, emailId, mobileNumber, landlineNumber, courseId, yearId, cityIdPass, stateIdPass, addressId)
    SELECT 
        Tab.Col.value('(studentId)[1]', 'bigint' ),
        Tab.Col.value('(studentFirstName)[1]', 'character varying(50)'),
        Tab.Col.value('(studentMiddleName)[1]', 'character varying(50)'),
        Tab.Col.value('(studentLastName)[1]', 'character varying(50)'),
        Tab.Col.value('(dateOfBirth)[1]', 'date'),
        Tab.Col.value('(emailId)[1]', 'character varying(50)'),
        Tab.Col.value('(mobileNumber)[1]', 'character varying(50)'),
        Tab.Col.value('(landlineNumber)[1]', 'character varying(50)'),
        Tab.Col.value('(courseId)[1]', 'BIGINT'),
        Tab.Col.value('(yearId)[1]', 'BIGINT'),
        Tab.Col.value('(cityIdPass)[1]', 'BIGINT'),
        Tab.Col.value('(stateIdPass)[1]', 'BIGINT'),
        Tab.Col.value('(addressId)[1]', 'BIGINT')

        from p_studentDetailsXML.nodes('/Root/StudentDetails') Tab(Col);

and How to make it work in postgresql function?

Any help?

What's this Tab.Col.Value() function doing? But you probably want the xpath() and xpath_table() functions — user330315
– user330315, Commented Mar 5, 2015 at 7:18
it's the syntax used in SQL Server to read the xml string pass to stored procedure from java and now i had to change database to postgres. So how can i read xml string in postgres function? — ITGeek
– ITGeek, Commented Mar 5, 2015 at 7:29

Tom-db · Accepted Answer · 2015-03-06 13:32:32Z

Postgres offers a good support for writing and reading xml data. See documentation for more info: www.postgresql.org/docs/current/static/functions-xml.html

The extension xml2 provide extra xml functionality: http://www.postgresql.org/docs/current/static/xml2.html. Create the extension with

CREATE EXTENSION xml2;

As a_horse_with_no_name said, you need probably the fuction xpath or xpath_table. Here a simple stored procedure example based on your data using the xpath function:

CREATE OR REPLACE FUNCTION insert_student(input_xml xml) RETURNS VOID AS
  $$
  DECLARE root_xpath TEXT = '/Root/StudentDetails/';
  BEGIN
    INSERT INTO temp_StudentDetails (studentId, studentFirstName)
    SELECT ((xpath(root_xpath|| 'StudentId/text()', input_xml))[1])::text::int,
          (xpath(root_xpath|| 'studentFirstName/text()', input_xml))[1];                
    RETURN;
  END;
  $$
LANGUAGE plpgsql;

So you can call the function:

SELECT insert_student('
<Root><StudentDetails>
  <StudentId>10</StudentId>
  <studentFirstName>John</studentFirstName>
</StudentDetails></Root>');

Collectives™ on Stack Overflow

How to read data from xml in postgresql function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related