Sunday, September 13, 2015

Retrieve XML data from database, Parse and display in OAF page



I recently came across a requirement to display XML data in an OAF page where each XML tag value will be displayed in separate UI components.

Though this is done for OAF screens, the below solution applies to ADF as well.

Requirement: Raw XML data is stored in the database in a column of type XMLTYPE. The XML data needs to be parsed and then display those values in OAF UI components.

Sample XML Data: 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
<?xml version="1.0" encoding="UTF-8" standalone='yes'?>
<order>
  <order_details>
    <order_num>2353</order_num>
    <currency>USD</currency>
    <line_details>
      <line_number>2949</line_number>
      <quantity>1</quantity>
    </line_details>
  </order_details>
</order>

Solution:
1.       Create a PL/SQL procedure to retrieve the XMLType data, convert to CLOB and return the CLOB value           

1
2
3
4
5
6
7
8
xml_out XMLTYPE;

SELECT xml
   INTO xml_out
  FROM xx_interface_log_tbl
WHERE log_id = TO_NUMBER (P_LOGID);

xml_out_clob:=  xml_out.getClobVal(); -- xml_out_clob, a CLOB variable which is the OUT parameter of the procedure.
      
2.       Build OAF page with all the components that corresponds to the XML tag values. As per the above example, we will have OrderNumber, Currency, Line_Number and Quantity.

3.       Create Transient VOs to hold the XML data. XXOrderHeaderVO and XXOrderLineVO

4.       In the processRequest of the page’s controller, get the ID of the record with which we can query the XML data from the table. Assume that the id is being passed as a parameter from the previous page. Invoke the AM method by passing the log_id

1
2
3
String pLogId = (String)pageContext.getParameter("ORD_LOG_ID");
Serializable[] funcParam = { pLogId };
am.invokeMethod("retrieveXML", funcParam);

5.     Below are the code snippets from the Application module which
a.       calls the PLSQL procedure to retrieve the XML data in CLOB format
b.      converts the CLOB into a Document.
c.       retrieves the values of XML elements and populate in VOs.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
public void retrieveXML(String logId) {
//Intialize Variables
        try {
            OracleCallableStatement stmt =
                (OracleCallableStatement)this.getDBTransaction().createCallableStatement("Begin XXSCM_RETRIEVE_ XML(:1,:2); end;", 10);
            stmt.registerOutParameter(1, OracleTypes.CLOB);
            stmt.setString(2, logId);
            stmt.execute();
            CLOB cl = (CLOB)stmt.getCLOB(1);
            Document doc = getOrdInbXmlDocument(cl);
            if (doc != null) {
                processOrderValues(doc);
            }        } catch (Exception e) {
//Handle Exception
        }
    }


    public Document getOrdInbXmlDocument(CLOB cl) {
        try {
            XMLType xmlType = XMLType.createXML(getCurrentConnection(), cl);
           String xmlStr = xmlType.getStringVal();
            DocumentBuilder docBuilder =
                DocumentBuilderFactory.newInstance().newDocumentBuilder();
            InputSource inputSource = new InputSource();
            inputSource.setCharacterStream(new StringReader(xmlStr));
            Document doc = docBuilder.parse(inputSource);
            return doc;
        } catch (Exception e) {
            //Handle Exception
        }
    }


    public void processOrderValues(Document doc) {
        NodeList orderNodes = doc.getElementsByTagName(“order_details”);//Tag Name. Refer the Sample data above
//Create new VO Header row, hdrRow
        for (int i = 0; i < orderNodes.getLength(); i++) {
            Element element = (Element)orderNodes.item(i);
        hdrRow.setOrderNum(getTagValue(element, “order_num”)); ”);//Tag Name
        hdrRow.setCurrency(getTagValue(element, “currency”)); ”);//Tag Name                 processOrderLineValues(element, hdrRow);

        }
    }

    public void processOrderLineValues(Element element,
                                       XXOrderHeaderVORowImpl hdrRow) {
        NodeList childNodes =
            element.getElementsByTagName(“line_details”); //Tag Name           
        for (int i = 0; i < childNodes.getLength(); i++) {
            //Create new VO Line row, linesRow
            Element lineElement = (Element)childNodes.item(i);
            linesRow.setLineNumber(getTagValue(lineElement,
                                                      “line_number”));
            linesRow.setQuantity(getTagValue(“quantity”));
        }
    }

    public String getTagValue(Element element, String tagName) {
        String tagValue = null;
        try {
            NodeList title = element.getElementsByTagName(tagName);
            Element line = (Element)title.item(0);
            tagValue = getCharacterDataFromElement(line);
        } catch (Exception e) {
            //Handle Exception
        }
        return tagValue;
    }

    public static String getCharacterDataFromElement(Element e) {
        Node child = e.getFirstChild();
        if (child instanceof CharacterData) {
            CharacterData cd = (CharacterData)child;
            return cd.getData();
        }
        return "";
    }

    private Connection getCurrentConnection() {
        Statement stmt = null;
        try {
            stmt = getDBTransaction().createStatement(0);
            return stmt.getConnection();
        } catch (Exception e) {
//handle exception
        } finally {
            if (stmt!= null)
                try {
                    stmt.close();
                } catch (Exception ex) {
//handle exception
                }
        }

    }


















Posted by



at





















Labels:
,
,
,
,

















No comments:















Post a Comment


















        

      









Subscribe to:
Post Comments (Atom)