Home » SQL & PL/SQL » SQL & PL/SQL » How to find this node value
How to find this node value [message #234481] Tue, 01 May 2007 14:27
jsingam
Messages: 1
Registered: May 2007
Junior Member
Gurus, I am trying to find the value of the node 'DATASTREAM.ACDUSRATTR'. Attached is my xml file:

I am using the construct:
DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, '//DATASTREAM.ACDUSRATTR[@accounted="*"][@index="1"]');


Please help me to find a solution. I have the XML file loaded into the table XXDBR_REQUISITION_IN;

BEGIN  
    SELECT REQ_XMLSEGMENT   INTO L_XML_REQ
    FROM XXDBR_REQUISITION_IN; 
    
    XPARSER := DBMS_XMLPARSER.NEWPARSER;   
    DBMS_XMLPARSER.PARSECLOB(XPARSER,L_XML_REQ);
    XMLDOC := DBMS_XMLPARSER.GETDOCUMENT(XPARSER);   
    DBMS_XMLPARSER.FREEPARSER(XPARSER);

    L_VERB := FIND_DOC_VALUE(xmldoc, '/ADD_REQUISITN_004/CNTROLAREA//VERB');   
    L_NOUN := FIND_DOC_VALUE(xmldoc, '//NOUN');
    DBMS_OUTPUT.Put_Line( L_VERB||'='||L_NOUN);
    
    L_REQ_REC.HEADER_ATTRIBUTE3 := FIND_DOC_VALUE(XMLDOC, '//REQUISTNID');
    DBMS_OUTPUT.PUT_LINE('REQ NUMBER, HEAD ATT3:'||L_REQ_REC.HEADER_ATTRIBUTE3);   
    
    L_REQ_REC.HEADER_DESCRIPTION := FIND_DOC_VALUE(XMLDOC, '//REQHEADER/DESCRIPTN');
    DBMS_OUTPUT.PUT_LINE('HEADER_DESCRIPTION:'||L_REQ_REC.HEADER_DESCRIPTION);  
    
    L_REQ_REC.HEADER_ATTRIBUTE2 := FIND_DOC_VALUE(XMLDOC, '//REQHEADER/POENTITY');
    DBMS_OUTPUT.PUT_LINE('ORG ID/HEAD ATT2:'||L_REQ_REC.HEADER_ATTRIBUTE2 );  
    
    
    L_APPROVER_ID := GET_PERSON_ID(FIND_DOC_VALUE(XMLDOC, '//REQHEADER/REQUESTER'));
    DBMS_OUTPUT.PUT_LINE('REQUESTER:'||L_APPROVER_ID );     
    
    L_PLANNER_ID := GET_PERSON_ID(FIND_DOC_VALUE(XMLDOC, '//REQHEADER/PLANNERID'));
    DBMS_OUTPUT.PUT_LINE('PLANNERID:'||L_PLANNER_ID );      


    L_HED_DATE_TIME := FIND_DOC_VALUE(xmldoc, '//REQHEADER/DATETIME/YEAR');
    DBMS_OUTPUT.Put_Line('DATETIME/YEAR:'||L_HED_DATE_TIME );          
    
    
    -- Get a list of all the requisition line nodes in the document
    L_REQ_LINES := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(XMLDOC),
                            '/ADD_REQUISITN_004//ADD_REQUISITN/REQLINE');

    FOR CUR_LINE IN 0 .. DBMS_XMLDOM.GETLENGTH(L_REQ_LINES) - 1 LOOP
        L_REQ_LINE := DBMS_XMLDOM.ITEM(L_REQ_LINES, CUR_LINE);
        L_REQ_LINES_TAB.extend;
        L_REQ_LINES_TAB(CUR_LINE+1).LINE_NUMBER := DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'REQLINENUM');
        L_REQ_LINES_TAB(CUR_LINE+1).DESCRIPTION := DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'DESCRIPTN'); 
        L_REQ_LINES_TAB(CUR_LINE+1).ITEM_NUMBER := (DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'ITEM'));      
        L_DEC_PLACE  := (DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'QUANTITY/NUMOFDEC'));  
        L_REQ_LINES_TAB(CUR_LINE+1).QUANTITY := DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'QUANTITY/VALUE')/
                                                SUBSTR(L_DEVIDER, 1, L_DEC_PLACE); 
                                                            
        L_DEC_PLACE := DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'OPERAMT/NUMOFDEC');  
        L_REQ_LINES_TAB(CUR_LINE+1).PRICE := DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'OPERAMT/VALUE')/
                                             SUBSTR(L_DEVIDER, 1, L_DEC_PLACE);         
        L_REQ_LINES_TAB(CUR_LINE+1).UOM := (DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'OPERAMT/UOM'));
         
        L_REQ_LINES_TAB(CUR_LINE+1).NEED_BY_DATE := 
                TO_DATE(DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'DATETIME/YEAR')||'-'||
                DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'DATETIME/MONTH')||'-'||
                DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, 'DATETIME/DAY'), 'YYYY-MM-DD');
        L_REQ_LINES_TAB(CUR_LINE+1).CHARGE_ACCOUNT_ID := GET_CCID(L_CCID_STR,CUR_LINE+1);
        
        
        DBMS_OUTPUT.Put_Line('pppp'||DBMS_XSLPROCESSOR.VALUEOF(L_REQ_LINE, '//DATASTREAM.ACDUSRATTR[@accounted="*"][@index="1"]'));
            
    END LOOP;


Thanks
Jagan Singam
  • Attachment: req4.xml
    (Size: 27.37KB, Downloaded 230 times)
Previous Topic: SQL How do I return all records between two times
Next Topic: Double Quotes in Data+External Tables+SQL
Goto Forum:
  


Current Time: Sat Dec 10 10:33:46 CST 2016

Total time taken to generate the page: 0.08454 seconds