I've written a stored procedure to store data from xml into tables. Followings are the DDL
CREATE TABLE STUDENT (
CONTROL_ID NUMBER(*,0),
BATCHS VARCHAR2(255),
FNAME VARCHAR2(255),
ROLL VARCHAR2(255),
CITY VARCHAR2(255),
CONSTRAINT PK_STUDENT PRIMARY KEY (CONTROL_ID, BATCHS)
);
CREATE TABLE MARKS_CHAR (
CONTROL_ID NUMBER(*,0),
BATCHS VARCHAR2(255),
SUBJECT VARCHAR2(255),
MARK_NUMBER NUMBER(*,0),
MARK_GRADE VARCHAR2(255),
UNIT VARCHAR2(255),
FOREIGN KEY (CONTROL_ID, BATCHS) REFERENCES STUDENT (CONTROL_ID, BATCHS)
);
CREATE SEQUENCE SEQ_STUDENT MINVALUE 1000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 3 START WITH 1000 CACHE 10 NOORDER NOCYCLE NOPARTITION ;
& Followings are the procedure and sample xml respectively
create or replace PROCEDURE STUDENT_MARKS_UPLOAD(XMLINPUT IN CLOB) AS
controlid INT;
BEGIN
SELECT SEQ_STUDENT.NEXTVAL INTO controlid FROM DUAL;
INSERT INTO STUDENT(CONTROL_ID,BATCHS,FNAME,ROLL,CITY)
SELECT controlid,x.* FROM xmltable (
XMLNAMESPACES('http://some.clg.india/statelevel' as "ns"),
'//ns:student'
PASSING xmltype(XMLINPUT)
COLUMNS
BATCHS VARCHAR2(255) PATH '//ns:header/ns:batch',
FNAME VARCHAR2(255) PATH '//ns:header/ns:name',
ROLL VARCHAR2(255) PATH '//ns:header/ns:roll',
CITY VARCHAR2(255) PATH '//ns:header/ns:city'
)x;
INSERT INTO MARKS_CHAR(CONTROL_ID,UNIT,BATCHS,SUBJECT,MARK_GRADE)
SELECT controlid,'string',x.* FROM xmltable (
XMLNAMESPACES('http://some.clg.india/statelevel' as "ns"),
'//ns:marks[ns:subject/text()!=''Humanities'' and ns:subject/text()!=''Values and Ethics'' and ((string-length(ns:gwrt/text())>0) and (string-length(ns:mnum/text())=0))]'
PASSING xmltype(XMLINPUT)
COLUMNS
BATCHS VARCHAR2(255) PATH './parent::*/ns:header/ns:batch',
SUBJECT VARCHAR2(255) PATH '//ns:subject',
MARK_GRADE VARCHAR2(2000) PATH '//ns:gwrt'
)x;
INSERT INTO MARKS_CHAR(CONTROL_ID,UNIT,BATCHS,SUBJECT,MARK_NUMBER)
SELECT controlid,'number',x.* FROM xmltable (
XMLNAMESPACES('http://some.clg.india/statelevel' as "ns"),
'//ns:marks[ns:subject/text()!=''Humanities'' and ns:subject/text()!=''Values and Ethics'' and ((string-length(ns:mnum/text())>0) and (string-length(ns:gwrt/text())=0))]'
PASSING xmltype(XMLINPUT)
COLUMNS
BATCHS VARCHAR2(255) PATH './parent::*/ns:header/ns:batch',
SUBJECT VARCHAR2(255) PATH '//ns:subject',
MARK_NUMBER INT PATH '//ns:mnum'
)x;
END ANNOUNCEITEMS_UPLOAD;
<class xmlns="http://some.clg.india/statelevel">
<student>
<header>
<name>Topesh</name>
<roll>0002</roll>
<batch>A001</batch>
<address>WB, India</address>
<city>Kolkata</city>
</header>
<marks>
<subject>Math</subject>
<mnum>85</mnum>
</marks>
<marks>
<subject>Language</subject>
<gwrt>A</gwrt>
</marks>
<marks>
<subject>Humanities</subject>
<mnum>89</mnum>
</marks>
<marks>
<subject>Geo</subject>
<gwrt>O</gwrt>
</marks>
<marks>
<subject>History</subject>
<gwrt>A+</gwrt>
</marks>
<marks>
<subject>Physics</subject>
<mnum>90</mnum>
</marks>
<marks>
<subject>Chemistry</subject>
<gwrt>B+</gwrt>
</marks>
<marks>
<subject>Values and Ethics</subject>
<mnum>93</mnum>
</marks>
</student>
<student>
<header>
<name>Subham</name>
<roll>0003</roll>
<batch>B002</batch>
<address>UP, India</address>
<city>Kanpur</city>
</header>
<marks>
<subject>Math</subject>
<mnum>98</mnum>
</marks>
<marks>
<subject>Language</subject>
<gwrt>C+</gwrt>
</marks>
<marks>
<subject>Humanities</subject>
<mnum>67</mnum>
</marks>
<marks>
<subject>Geo</subject>
<gwrt>E</gwrt>
</marks>
<marks>
<subject>History</subject>
<gwrt>A</gwrt>
</marks>
<marks>
<subject>Physics</subject>
<mnum>97</mnum>
</marks>
<marks>
<subject>Chemistry</subject>
<gwrt>O</gwrt>
</marks>
<marks>
<subject>Values and Ethics</subject>
<mnum>58</mnum>
</marks>
</student>
<student>
<header>
<name>Vinay</name>
<roll>0001</roll>
<batch>C003</batch>
<address>Kerakla, India</address>
<city>Bangalore</city>
</header>
<marks>
<subject>Math</subject>
<mnum>99</mnum>
</marks>
<marks>
<subject>Language</subject>
<gwrt>B</gwrt>
</marks>
<marks>
<subject>Humanities</subject>
<mnum>81</mnum>
</marks>
<marks>
<subject>Geo</subject>
<gwrt>E</gwrt>
</marks>
<marks>
<subject>History</subject>
<gwrt>E</gwrt>
</marks>
<marks>
<subject>Physics</subject>
<mnum>92</mnum>
</marks>
<marks>
<subject>Chemistry</subject>
<gwrt>E</gwrt>
</marks>
<marks>
<subject>Values and Ethics</subject>
<mnum>87</mnum>
</marks>
</student>
</class>
The output is as follows:
For the table STUDENT
CONTROL_ID BATCHS FNAME ROLL CITY 1000 A001 Topesh 0002 Kolkata 1000 B002 Subham 0003 Kanpur 1000 C003 Vinay 0001 BangaloreFor the table MARKS_CHAR
CONTROL_ID BATCHS SUBJECT MARK_NUMBER MARK_GRADE UNIT 1000 A001 Language A string 1000 A001 Geo O string 1000 A001 History A+ string 1000 A001 Chemistry B+ string 1000 B002 Language C+ string 1000 B002 Geo E string 1000 B002 History A string 1000 B002 Chemistry O string 1000 C003 Language B string 1000 C003 Geo E string 1000 C003 History E string 1000 C003 Chemistry E string 1000 A001 Math 85 number 1000 A001 Physics 90 number 1000 B002 Math 98 number 1000 B002 Physics 97 number 1000 C003 Math 99 number 1000 C003 Physics 92 number
But the requirement is for each student segment, there should be a unique CONTROL_ID, i.e., the desired output will be
CONTROL_ID BATCHS FNAME ROLL CITY
1000 A001 Topesh 0002 Kolkata
1003 B002 Subham 0003 Kanpur
1006 C003 Vinay 0001 Bangalore
and
CONTROL_ID BATCHS SUBJECT MARK_NUMBER MARK_GRADE UNIT
1000 A001 Language A string
1000 A001 Geo O string
1000 A001 History A+ string
1000 A001 Chemistry B+ string
1003 B002 Language C+ string
1003 B002 Geo E string
1003 B002 History A string
1003 B002 Chemistry O string
1006 C003 Language B string
1006 C003 Geo E string
1006 C003 History E string
1006 C003 Chemistry E string
1000 A001 Math 85 number
1000 A001 Physics 90 number
1003 B002 Math 98 number
1003 B002 Physics 97 number
1006 C003 Math 99 number
1006 C003 Physics 92 number
Please help how to achieve this in the procedure without changing any schema. I am using SQLDeveloper
