CODEWITHSUNDEEP
xmlpostgresqlstored-procedures
ITGeek
ITGeek

Reputation: 45

How to read data from xml in postgresql function?

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?

Upvotes: 1

Views: 8179

Answers (1)

Tom-db
Tom-db

Reputation: 6868

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>');

Upvotes: 2

Related Questions