I'm unable to extract the data from the XML below is my code kindly help.
So basically I need data from a few tags.
DECLARE
l_clob clob := '<list-details xmlns="http://ema.europa.eu/schema/spor">
<list-information>
<list-id id="200000000006">
<link href="https://spor-uat.ema.europa.eu/v1/lists/200000000006" />
</list-id>
</list-information>
</list-details>';--schema-version="2.0"
lv_root CONSTANT VARCHAR2(1000) := '/ns1:list-details/*';
lv_namespaces CONSTANT VARCHAR2(1000) := 'xmlns:ns1="http://ema.europa.eu/schema/spor"';
lv_value varchar2(100);
l_parser dbms_xmlparser.Parser := dbms_xmlparser.newParser;
l_doc dbms_xmldom.DOMDocument;
l_nl dbms_xmldom.DOMNodeList;
l_n dbms_xmldom.DOMNode;
lv_rows_inserted_cnt NUMBER := 0;
lv_rows_processed_cnt NUMBER;
BEGIN
dbms_xmlparser.parseClob(l_parser, l_clob);
l_doc := dbms_xmlparser.getDocument(l_parser);
dbms_xmlparser.freeParser(l_parser);
l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc), lv_root, lv_namespaces);
FOR cur_rec IN 0 .. dbms_xmldom.getLength(l_nl) - 1
LOOP
l_n := dbms_xmldom.item(l_nl, cur_rec);
lv_rows_inserted_cnt := lv_rows_inserted_cnt + 1;
--dbms_xslprocessor.valueOf(l_n, '@id', lv_value);
--dbms_output.put_line('id : '||lv_value);
dbms_output.put_line(dbms_xmldom.getNodeValue(dbms_xmldom.getFirstChild(l_n)));
END LOOP;
lv_rows_processed_cnt := lv_rows_inserted_cnt;
dbms_xmldom.freeDocument(l_doc);
dbms_output.put_line(lv_rows_processed_cnt||' Rows Parsed ');
END;
/