How to Reading XML data using PLSQL
Create a UTL Directory:
This involves the following steps. Creating a Logical directory and giving the Permissions to access this directory using an URL. Using the XML Dom Parser Procedures to read the Xml file , parse it
and then load it into the respective columns.
1) Create a logical directory
Create or replace directory TEST_LOB_DIR as ‘/data/tst1/attachment’
It creates a directory with Owner SYS.Grant privileges on this directory to Apps.
2) Create a physical directory at the given location ‘/data/tst1/attachment’
Permissions on this directory too.This directory has to be a UTL directory.
3) Edit the conf file at this path
Cd $IAS_ORACLE_HOME/Apache/Apache/conf
==>/apps/applmgr/tst1ora/IAS/Apache/Apache/conf
Edit the file apps.conf
Alias /attachment/ “/data/tst1/attachment/”
<Location /attachment/>
Order allow, deny
Allow from all
</Location>
4) Bounce the Apache.
Create a procedure to parse and retrieve the XML data and then insert it into appropriate columns in the database.
Used two PLSQL packages for these –
1. XMLPARSER
2. XMLDOM
1. XMLParser – Procedures
i.)parse(p Parser, url VARCHAR2)
Description – The parse procedure takes two parameters which are the parse object and the url of the
xml file that has to parsed.
ii.) setValidationMode(p Parser, yes BOOLEAN)
Checks whether XML is Valid or not.
iii.) setBaseDir(p Parser, dir VARCHAR2)
Sets the base url or directory path.
iv.) getDocument(p Parser)
Get the Document which has to be parsed.
2. XMLDOM
DOMDocument
DOMELEMENT
DOMNODELIST
DOMNODE
DOMNamedNodeMap
FUNCTIONS –
getNodeName(n DOMNode) RETURN VARCHAR2 – Retrieves the Name of the Node
getNodeValue(n DOMNode) RETURN VARCHAR2 – Retrieves the Value of the Node
getElementsByTagName(doc DOMDocument, tagname IN VARCHAR2)- Retrieves the elements in
the by tag name
getDocumentElement(doc DOMDocument) RETURN DOMElement – Retrieves the root element of
the document
getFirstChild(n DOMNode) RETURN DOMNode – Retrieves the first child of the node
getLength(nl DOMNodeList) RETURN NUMBER- Retrieves the number of items in the list.
Sample XML File
Leave a Reply
Want to join the discussion?Feel free to contribute!