xml - Using XMLTYPE to find repeating nodes -
i have piece code want use repeating nodes. far have manage hard code substring find value firstname , surname name type = 'spelling variation'. other attempts have failed various errors , null values being returned. there way without have supply [1], [2], [3], [?] @ all.
many info passed on.
-------------------------------------------------------------------------------------- declare x xmltype := xmltype('<?xml version="1.0" ?> <records> <person id="34463" action="chg" date="23-nov-2009"> <gender>female</gender> <activestatus>inactive</activestatus> <deceased>no</deceased> <namedetails> <name nametype="primary name"> <namevalue> <firstname>34463firstname</firstname> <surname>34463surname</surname> </namevalue> </name> <name nametype="spelling variation"> <namevalue> <firstname>34463firstnamevar1</firstname> <surname>34463surnamevar1</surname> </namevalue> <namevalue> <firstname>34463firstnamevar2</firstname> <surname>34463surnamevar2</surname> </namevalue> </name> </namedetails> </person> <person id="34464" action="chg" date="23-nov-2009"> <gender>male</gender> <activestatus>inactive</activestatus> <deceased>no</deceased> <namedetails> <name nametype="primary name"> <namevalue> <firstname>34464firstname</firstname> <surname>34464surname</surname> </namevalue> </name> <name nametype="spelling variation"> <namevalue> <firstname>34464firstnamevar1</firstname> <surname>34464surnamevar1</surname> </namevalue> <namevalue> <firstname>34464firstnamevar2</firstname> <surname>34464surnamevar2</surname> </namevalue> </name> </namedetails> </person> </records>'); begin get_id in (select extractvalue(value(id),'/person/@id') id table(xmlsequence(extract(x,'/records/person'))) id ) loop -- dbms_output.put_line(get_id.id); get_gender in (select extractvalue(value(gender),'/person/gender') gender table(xmlsequence(extract(x,'/records/person'))) gender extractvalue(value(gender),'/person/@id') =get_id.id) loop dbms_output.put_line(get_gender.gender); end loop; get_name in (select extractvalue(value(fname),'/namevalue/firstname') fname table(xmlsequence(extract (x,'/records/person/namedetails/name/namevalue'))) fname extractvalue(value(fname),'/person/@id') = get_id.id) loop dbms_output.put_line('name '||get_name.fname); end loop; get_name in (--pick out primary name select extractvalue(value(xname),'/name/namevalue/firstname') fname ,extractvalue(value(xname),'/name/namevalue/surname') sname ,extractvalue(value(xname),'/name/namevalue/originalscriptname') oname table(xmlsequence(extract( (--get person tag select xmltype(xperson.object_value.getstringval() ) table(xmlsequence(extract(x,'/records/person'))) xperson extractvalue(value(xperson),'/person/@id') = get_id.id ) ,'person/namedetails/name'))) xname extractvalue(value(xname),'/name/@nametype') in ('primary name') ) loop dbms_output.put_line(get_name.fname||' '||get_name.sname||' '||get_name.oname); dbms_output.put_line(' '); end loop; get_name in (--pick out spelling variation select extractvalue(value(xname),'/name/namevalue[1]/firstname') fname1 ,extractvalue(value(xname),'/name/namevalue[1]/surname') sname1 ,extractvalue(value(xname),'/name/namevalue[2]/firstname') fname2 ,extractvalue(value(xname),'/name/namevalue[2]/surname') sname2 ,extractvalue(value(xname),'/name/namevalue[3]/firstname') fname3 ,extractvalue(value(xname),'/name/namevalue[3]/surname') sname3 table(xmlsequence(extract( (--get person tag select xmltype(xperson.object_value.getstringval() ) table(xmlsequence(extract(x,'/records/person'))) xperson extractvalue(value(xperson),'/person/@id') = get_id.id ) ,'person/namedetails/name'))) xname extractvalue(value(xname),'/name/@nametype') in ('spelling variation') ) loop dbms_output.put_line(get_name.fname1||' '||get_name.sname1); dbms_output.put_line(get_name.fname2||' '||get_name.sname2); dbms_output.put_line(get_name.fname3||' '||get_name.sname3); dbms_output.put_line(' '); end loop; dbms_output.put_line(' '); end loop; end;
declare x xmltype := xmltype('<?xml version="1.0" ?> <records> <person id="34463" action="chg" date="23-nov-2009"> <gender>female</gender> <activestatus>inactive</activestatus> <deceased>no</deceased> <namedetails> <name nametype="primary name"> <namevalue> <firstname>34463firstname</firstname> <surname>34463surname</surname> </namevalue> </name> <name nametype="spelling variation"> <namevalue> <firstname>34463firstnamevar1</firstname> <surname>34463surnamevar1</surname> </namevalue> <namevalue> <firstname>34463firstnamevar2</firstname> <surname>34463surnamevar2</surname> </namevalue> </name> </namedetails> </person> <person id="34464" action="chg" date="23-nov-2009"> <gender>male</gender> <activestatus>inactive</activestatus> <deceased>no</deceased> <namedetails> <name nametype="primary name"> <namevalue> <firstname>34464firstname</firstname> <surname>34464surname</surname> </namevalue> </name> <name nametype="spelling variation"> <namevalue> <firstname>34464firstnamevar1</firstname> <surname>34464surnamevar1</surname> </namevalue> <namevalue> <firstname>34464firstnamevar2</firstname> <surname>34464surnamevar2</surname> </namevalue> </name> </namedetails> </person> </records>'); begin get_id in ( select * xmltable('/records/person' passing x columns id number path '@id', gender varchar2(64) path 'gender', person_xml$ xmltype path '.' ) ) loop dbms_output.put_line(get_id.id); dbms_output.put_line(get_id.gender); get_name_type in ( select * xmltable('person/namedetails/name' passing get_id.person_xml$ columns name_type nvarchar2(128) path '@nametype', name_values xmltype path '.' ) order decode(name_type, 'primary name',1, 'spelling variation',2, 99) asc ) loop dbms_output.put_line('--- '||get_name_type.name_type); get_name in ( select * xmltable('name/namevalue' passing get_name_type.name_values columns first_name nvarchar2(128) path 'firstname', surname nvarchar2(128) path 'surname', orig_scr_name nvarchar2(128) path 'originalscriptname' ) ) loop dbms_output.put_line(get_name.first_name||' '||get_name.surname||' '||get_name.orig_scr_name); end loop; end loop; end loop; end; /
Comments
Post a Comment