Parsing XML Comments in Oracle [duplicate]

Question

This question already has answers here:

Closed 7 years ago.

Possible Duplicate:
XML parsing in oracle pl/sql

In the below XML I need to get the Comment "ADOR Acknowledgement 2"

Can you please help me to get this

<?xml version="1.0" encoding="utf-8"?> 
<!--ADOR Acknowledgement 2-->  
<AckTransmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.irs.gov/efile">
<TransmissionHeader recordCount="1">  
<Jurisdiction>ALABAMA</Jurisdiction>  
<TransmissionId>1946157056</TransmissionId>    
<Timestamp>2012-08-16T01:25:47-05:00</Timestamp> 
<Transmitter>      
  <ETIN>00000</ETIN>     
</Transmitter>  
<ProcessType>T</ProcessType>  
<AgentIdentifier>ACK</AgentIdentifier> 
</TransmissionHeader> 
<Acknowledgement> 
  <SubmissionId>X1684956672</SubmissionId> 
  <EFIN>X16849</EFIN>  
  <GovernmentCode>ALST</GovernmentCode> 
  <SubmissionType>XMLTOM</SubmissionType>
  <TaxYear>9999</TaxYear>  
  <SubmissionCategory>MFET</SubmissionCategory>   
  <AcceptanceStatus>A</AcceptanceStatus>  
  <ContainedAlerts>0</ContainedAlerts>    
  <StatusDate>2012-08-16</StatusDate>   
</Acknowledgement> 
</AckTransmission>

Answer 1

Applying too many functions on the XML text may adversely affect performance, so as an alternative, something like this should work-

SQL> create table my_xml of xmltype;

Table created.

SQL> insert into my_xml values (xmltype('<?xml version="1.0" encoding="utf-8"?> 
                    <!--ADOR Acknowledgement 2-->  
                    <AckTransmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.irs.gov/efile">
                    <TransmissionHeader recordCount="1">  
                    <Jurisdiction>ALABAMA</Jurisdiction>  
                    <TransmissionId>1946157056</TransmissionId>    
                    <Timestamp>2012-08-16T01:25:47-05:00</Timestamp> 
                    <Transmitter>      
                      <ETIN>00000</ETIN>     
                    </Transmitter>  
                    <ProcessType>T</ProcessType>  
                    <AgentIdentifier>ACK</AgentIdentifier> 
                    </TransmissionHeader> 
                    <Acknowledgement> 
                      <SubmissionId>X1684956672</SubmissionId> 
                      <EFIN>X16849</EFIN>  
                      <GovernmentCode>ALST</GovernmentCode> 
                      <SubmissionType>XMLTOM</SubmissionType>
                      <TaxYear>9999</TaxYear>  
                      <SubmissionCategory>MFET</SubmissionCategory>   
                      <AcceptanceStatus>A</AcceptanceStatus>  
                      <ContainedAlerts>0</ContainedAlerts>    
                      <StatusDate>2012-08-16</StatusDate>   
                    </Acknowledgement> 
                    </AckTransmission>'));

1 row(s) inserted.      

SQL> select x.comment_text
  from my_xml t
       , xmltable(
         '/descendant::comment()'
          passing t.object_value
          columns comment_text varchar2(200) path '.'
        ) x
   ;

COMMENT_TEXT
----------------------------
ADOR Acknowledgement 2

Answer 2

here is the code to parse the comment from xml.

import java.io.IOException;
import java.io.StringReader;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.ParserConfigurationException;



import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

import org.w3c.dom.NodeList;

import org.xml.sax.InputSource;

import org.xml.sax.SAXException;

public class ParseComment {

public static void xmlParser(String xmlString) {
    DocumentBuilderFactory builderFactory = DocumentBuilderFactory
            .newInstance();
    Document document = null;
    try {
        DocumentBuilder documentBuilder = builderFactory
                .newDocumentBuilder();
        InputSource source = new InputSource();
        source.setCharacterStream(new StringReader(xmlString));
        document = documentBuilder.parse(source);
        NodeList nodes = document.getChildNodes();
        for (int i = 0; i < nodes.getLength(); i++) {
            if (nodes.item(i) instanceof Element) {
                Element element = (Element) nodes.item(i);
                /* add your logic to parse element values here.*/
                System.out.println("Element : " + element.getTextContent());// nodes.item(i).getTextContent());

            }else{
                // This is the comment string.
                String commentString = nodes.item(i).getTextContent();
                System.out.println("Comment: "+commentString);
            }
        }
    } catch (ParserConfigurationException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (SAXException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}



}

Answer 3

A combination of replace, extract and and xpath expression could do:

select 
replace(replace(
extract(
xmltype (q'{<?xml version="1.0" encoding="utf-8"?> 
<!--ADOR Acknowledgement 2-->  
<AckTransmission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.irs.gov/efile">
<TransmissionHeader recordCount="1">  
<Jurisdiction>ALABAMA</Jurisdiction>  
<TransmissionId>1946157056</TransmissionId>    
<Timestamp>2012-08-16T01:25:47-05:00</Timestamp> 
<Transmitter>      
  <ETIN>00000</ETIN>     
</Transmitter>  
<ProcessType>T</ProcessType>  
<AgentIdentifier>ACK</AgentIdentifier> 
</TransmissionHeader> 
<Acknowledgement> 
  <SubmissionId>X1684956672</SubmissionId> 
  <EFIN>X16849</EFIN>  
  <GovernmentCode>ALST</GovernmentCode> 
  <SubmissionType>XMLTOM</SubmissionType>
  <TaxYear>9999</TaxYear>  
  <SubmissionCategory>MFET</SubmissionCategory>   
  <AcceptanceStatus>A</AcceptanceStatus>  
  <ContainedAlerts>0</ContainedAlerts>    
  <StatusDate>2012-08-16</StatusDate>   
</Acknowledgement> 
</AckTransmission>}'),
'/descendant::comment()'
), '<!--'), '-->') as comment_
from dual;