I'm trying to parse an XML (only provided part of the XML) response and return the values. My second set of code snippets does insert nulls into the table so I am looping over some node but I want the value of memberID or any value for that matter.
It would help if someone could explain how to get the values so I know how to do it.
<?xml version='1.0' encoding='utf-8'?>
<soapenv:Envelope xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope">
<soapenv:Body>
<ns:getGradesResponse
xmlns:ns="http://gradebook.ws.blackboard"
xmlns:ax217="http://persist.blackboard/xsd"
xmlns:ax216="http://gradebook.ws.blackboard/xsd"
xmlns:ax220="http://authentication.security.platform.blackboard/xsd"
xmlns:ax215="http://ws.platform.blackboard/xsd"
xmlns:ax219="http://gradebook2.platform.blackboard/xsd"
xmlns:ax218="http://base.blackboard/xsd">
<ns:return type="blackboard.ws.gradebook.ScoreVO">
<ax216:averageScore>95.0</ax216:averageScore>
<ax216:columnId>_38303_1</ax216:columnId>
<ax216:courseId xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<ax216:exempt>false</ax216:exempt>
<ax216:expansionData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<ax216:firstAttemptId>_48_1</ax216:firstAttemptId>
<ax216:grade>95.0</ax216:grade>
<ax216:highestAttemptId>_48_1</ax216:highestAttemptId>
<ax216:id>_48_1</ax216:id>
<ax216:instructorComments xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<ax216:lastAttemptId>_48_1</ax216:lastAttemptId>
<ax216:lowestAttemptId>_48_1</ax216:lowestAttemptId>
<ax216:manualGrade xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<ax216:manualScore xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
<ax216:memberId>_35264_1</ax216:memberId>
<ax216:schemaGradeValue>A</ax216:schemaGradeValue>
<soapenv:Envelope><soapenv:Body><ns:getGradesResponse><ns:return type="blackboard.ws.gradebook.ScoreVO"><ax216:averageScore>95.0</ax216:averageScore><ax216:columnId>_38303_1</ax216:columnId><ax216:courseId xsi:nil="true"/><ax216:exempt>false</ax216:exempt><ax216:expansionData xsi:nil="true"/><ax216:firstAttemptId>_48_1</ax216:firstAttemptId><ax216:grade>95.0</ax216:grade><ax216:highestAttemptId>_48_1</ax216:highestAttemptId><ax216:id>_48_1</ax216:id><ax216:instructorComments xsi:nil="true"/><ax216:lastAttemptId>_48_1</ax216:lastAttemptId><ax216:lowestAttemptId>_48_1</ax216:lowestAttemptId><ax216:manualGrade xsi:nil="true"/><ax216:manualScore xsi:nil="true"/><ax216:memberId>_35264_1</ax216:memberId><ax216:schemaGradeValue>A</ax216:schemaGradeValue><ax216:shortInstructorComments xsi:nil="true"/><ax216:shortStudentComments xsi:nil="true"/><ax216:status>1</ax216:status><ax216:studentComments xsi:nil="true"/><ax216:userId xsi:nil="true"/></ns:return><ns:return type="blackboard.ws.gradebook.ScoreVO"><ax216:averageScore>65.0</ax216:averageScore><ax216:columnId>_38303_1</ax216:columnId><ax216:courseId xsi:nil="true"/><ax216:exempt>false</ax216:exempt><ax216:expansionData xsi:nil="true"/><ax216:firstAttemptId>_26_1</ax216:firstAttemptId><ax216:grade>65.0</ax216:grade><ax216:highestAttemptId>_26_1</ax216:highestAttemptId><ax216:id>_26_1</ax216:id><ax216:instructorComments xsi:nil="true"/><ax216:lastAttemptId>_26_1</ax216:lastAttemptId><ax216:lowestAttemptId>_26_1</ax216:lowestAttemptId><ax216:manualGrade xsi:nil="true"/><ax216:manualScore xsi:nil="true"/><ax216:memberId>_35751_1</ax216:memberId><ax216:schemaGradeValue>D</ax216:schemaGradeValue><ax216:shortInstructorComments xsi:nil="true"/><ax216:shortStudentComments xsi:nil="true"/><ax216:status>1</ax216:status><ax216:studentComments xsi:nil="true"/><ax216:userId xsi:nil="true"/></ns:return>
for r in (select value(p) as id
from table(xmlsequence(extract(XMLResponse, '/soapenv:Envelope/soapenv:Body/ns:getGradesResponse/ns:return',
'xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope",
xmlns:ax216="http://gradebook.ws.blackboard/xsd",
xmlns:ns="http://gradebook.ws.blackboard"'))) p)
loop
insert into xwarehouses
values
(r.id);
commit;
The above code returns:
<ns:return xmlns:ns="http://gradebook.ws.blackboard" type="blackboard.ws.gradebook.ScoreVO">
<ax216:averageScore xmlns:ax216="http://gradebook.ws.blackboard/xsd">85.0</ax216:averageScore>
<ax216:columnId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_38303_1</ax216:columnId>
<ax216:courseId xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:exempt xmlns:ax216="http://gradebook.ws.blackboard/xsd">false</ax216:exempt>
<ax216:expansionData xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:firstAttemptId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_46_1</ax216:firstAttemptId>
<ax216:grade xmlns:ax216="http://gradebook.ws.blackboard/xsd">85.0</ax216:grade>
<ax216:highestAttemptId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_46_1</ax216:highestAttemptId>
<ax216:id xmlns:ax216="http://gradebook.ws.blackboard/xsd">_46_1</ax216:id>
<ax216:instructorComments xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:lastAttemptId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_46_1</ax216:lastAttemptId>
<ax216:lowestAttemptId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_46_1</ax216:lowestAttemptId>
<ax216:manualGrade xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:manualScore xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:memberId xmlns:ax216="http://gradebook.ws.blackboard/xsd">_91921_1</ax216:memberId>
<ax216:schemaGradeValue xmlns:ax216="http://gradebook.ws.blackboard/xsd">B</ax216:schemaGradeValue>
<ax216:shortInstructorComments xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:shortStudentComments xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:status xmlns:ax216="http://gradebook.ws.blackboard/xsd">1</ax216:status>
<ax216:studentComments xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
<ax216:userId xmlns:ax216="http://gradebook.ws.blackboard/xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>
</ns:return>
This query returns nulls.
for r in (select extractvalue(value(p), 'memberId/text()') as id
from table(xmlsequence(extract(XMLResponse, '/soapenv:Envelope/soapenv:Body/ns:getGradesResponse/ns:return',
'xmlns:soapenv="http://www.w3.org/2003/05/soap-envelope",
xmlns:ax216="http://gradebook.ws.blackboard/xsd",
xmlns:ns="http://gradebook.ws.blackboard"'))) p)
loop
insert into ws_grades
(value)
values
(r.id);
commit;
end loop;