Home » Developer & Programmer » JDeveloper, Java & XML » XML Parsing Help (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0)
XML Parsing Help [message #642777] Fri, 18 September 2015 17:11 Go to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
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;

Re: XML Parsing Help [message #642778 is a reply to message #642777] Fri, 18 September 2015 18:40 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
I'm betting I need two loops since each student has a <ns:return></ns:return> and then loop again to get to the inner elements.

I don't think that is correct either. One loop for the <ns:return></ns:return> elements.

[Updated on: Fri, 18 September 2015 18:45]

Report message to a moderator

Re: XML Parsing Help [message #642908 is a reply to message #642778] Tue, 22 September 2015 21:06 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
Any help?
Re: XML Parsing Help [message #642972 is a reply to message #642908] Wed, 23 September 2015 10:16 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
You could try to add the namespace:

select extractvalue(value(p), '//ax216:memberId/text()','xmlns:ax216="http://gradebook.ws.blackboard/xsd"') 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;

[Updated on: Wed, 23 September 2015 10:18]

Report message to a moderator

Re: XML Parsing Help [message #642974 is a reply to message #642972] Wed, 23 September 2015 16:13 Go to previous messageGo to next message
Duane
Messages: 557
Registered: December 2002
Senior Member
That worked. Now that I can see how you did it I can apply that to my other code.

Thanks.
Re: XML Parsing Help [message #643259 is a reply to message #642972] Thu, 01 October 2015 21:24 Go to previous message
Duane
Messages: 557
Registered: December 2002
Senior Member
_jum wrote on Wed, 23 September 2015 10:16
You could try to add the namespace:

select extractvalue(value(p), '//ax216:memberId/text()','xmlns:ax216="http://gradebook.ws.blackboard/xsd"') 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;


xmlns:ax216="http://gradebook.ws.blackboard/xsd"
//ax216:memberId/text()

Is there anything I can do if this number is changing? I believe what is happening is the web service is on applications servers so the number changes based on what application server you are hitting. The number was 216 but is now 217 and I have seen the number change for other XML responses.
Previous Topic: how can we iterate viewobject rows in different way in adf
Next Topic: XSD type reference
Goto Forum:
  


Current Time: Thu Mar 28 04:08:05 CDT 2024