XML - PL / SQL How to get the subnodes of the nodes

Navigation
1

I am trying to generate a dynamic insert by taking an XML file but I can not extract the merchandise attributes for another string. So far I was able to extract the first 4

You have to follow this structure

The statement is as follows: 

--Parseo el contenido del documento xml
v_parse := xmlparser.newparser();
xmlparser.parseclob(v_parse, v_xml);

--Obtenemos el documento xml cargado en un objeto DOM
v_doc := xmlparser.getdocument(v_parse);
xmlparser.freeparser(v_parse);
v_nodos := xmldom.getChildrenByTagName(xmldom.getDocumentelement(v_doc), '*');

--Recorremos cada nodo del arreglo, el arreglo inicio en el item 0  --- Aca devuelve 7
FOR j IN 1 ..  xmldom.getlength(v_nodos) loop

    v_nodo := xmldom.item(v_nodos,j-1);
    -- obtenemos la lista d eelementos que tiene el nodo actual
    v_nodos_hijo := xmldom.getchildnodes(v_nodo);

    --recorremos los  atributos del nodo, obtenemos la lista de atributos que tiene el nodo
    v_atributos := xmldom.getattributes(v_nodo);

    --recorremos cada atributo del nodo
    vv_linea := 'INSERT INTO Fundamento (CODIGO, DESCRIPCION, FEC_INI, FEC_FIN, COD_MONEDA) VALUES (' ;

    FOR k IN 1 .. xmldom.getlength(v_nodos_hijo) LOOP
        --obtenemos un elemento
        v_elemento := xmldom.item(v_nodos_hijo, k-1);   

        IF k = 1 THEN 
            vv_linea :=  vv_linea ||CHR(39)||xmldom.getnodevalue(xmldom.getfirstchild(v_elemento))||CHR(39)||',' ;
        ELSIF k = 2 THEN 
            vv_linea :=  vv_linea ||CHR(39)||xmldom.getnodevalue(xmldom.getfirstchild(v_elemento))||CHR(39)||',' ;
        ELSIF k = 3 THEN 
            vv_linea :=  vv_linea ||'TO_DATE('||CHR(39)||xmldom.getnodevalue(xmldom.getfirstchild(v_elemento))||CHR(39)||','||CHR(39)||'dd/MM/yyyy'||CHR(39)||'),' ;
        ELSIF k = 4 THEN 
            vv_linea :=  vv_linea ||'TO_DATE('||CHR(39)||xmldom.getnodevalue(xmldom.getfirstchild(v_elemento))||CHR(39)||','||CHR(39)||'dd/MM/yyyy'||CHR(39)||'))';
        END IF;

        vv_linea := REPLACE (vv_linea, 'TO_DATE('||CHR(39)||'-'||CHR(39)||','||CHR(39)||'dd/MM/yyyy'||CHR(39)||')' , 'NULL');

    END LOOP;

    dbms_output.put_line(vv_linea);

END LOOP;

END;

Could someone help me know what methods I should use to extract the merchandise section?

Thank you!

    
asked by Alejandro Mattioli 04.09.2017 в 17:33
source

1 answer

0

A clob does not help you store an XML and use XML functions in oracle. A data type% co_of% is better. Example creates a table like this:

xmlType

Then you insert an XML like this:

CREATE TABLE Person ( id int primary key, nombre varchar2(20) not null, direccion xmlType);

Once this is done you can already do querys using XPATH like this:

INSERT INTO Person(id,nombre,direccion) VALUES(1,'Peter Smith',XMLTYPE( '<MailAddressTo id="1"> <Person>Peter Smith</Person> <Street>10 Downing Street</Street> <Street>20 Principal Avenue</Street> <Street>30 Diagonal Street</Street> <Street>40 Satelite Street</Street> <Street>50 Politecnical Street</Street> <City>London</City> <State>England</State> <Zipcode>22334</Zipcode> </MailAddressTo>' ));

Or these updates:

SELECT extract(DIRECCION, '/MailAddressTo/Street[1]'),--expresion XPATH accede al 1er tag de la lista extractvalue(DIRECCION, '/MailAddressTo/Street[4]')--expresion XPATH accede al 4to tag de la lista into ValorTag, svalor FROM person;

UPDATE person SET DIRECCION = deleteXML(direccion,'/MailAddressTo/City');--borrando el tag city del xml

Or like this example:

UPDATE person SET DIRECCION = deleteXML(direccion,'/MailAddressTo/Street[4]');--borrando el 4to tag street de la lista con XPATH;

You can also ask if there is a node:

UPDATE person SET direccion = updateXML(direccion, '/MailAddressTo/Person/text()', 'Stephen G. King',--actualiza el tag person con un nuevo nombre '/MailAddressTo/@id', '2');--actualiza la propiedad id del tag MailAddressTo a 2

I hope you can use these examples.

    
answered by 04.09.2017 в 18:16
android.content.res.Resources $ NotFoundException error [closed] Android virtual machine