How to Parse a Xml in Oracle Using Xpath with Xmltype - Free Essay Example

Sample details Pages: 2 Words: 583 Downloads: 2 Date added: 2017/09/13 Category Advertising Essay Did you like this example? Old school DOM ! Traditionally Xml parsing was doing using dom objects in Pl/Sql level. This method of parsing XMLs has significant performance hit, even if we left out the many loops that we have to go through to get a value of a single XML element. Enough of Dom, Lest see abut XMLtype. Oracle 9i and above has this new type of object which is capable of recognizing a XML document end to end. This is the link to the document. https://download. oracle. com/docs/cd/B10501_01/appdev. 920/a96620/xdb04cre. htm And if u don’t have time to read it your self I have red it and these are some available functions to extract values safely from a XML. And all the code you see is PLSQL 1. XML is represented as CLOB. Take the below XML as an example. The xml need (well there might be other ways too) to be provided as clob to the XML type constructor. l_clob CLOB := 123456789 AIR USA123 1 2 3 2 DUPLEX_VERTICAL EMAIL [emailprotected] com Don’t Write Blog Posts at work Please do not do any thing other than work while you are at work [emailprotected] com OTHER SMS Don’t waste time! Our writers will create an original "How to Parse a Xml in Oracle Using Xpath with Xmltype" essay for you Create order SMS_SENDER1 ’;2. Lest Start the fire. Creating XML object. xml_object SYS. Xmltype; xml_object:=sys. xmltype. createXML(l_clob);3. Accessing a value of a XML element. If you do this in the proper safe way this is actually three steps. Here are the steps. Check if the Item that you are trying to access exits. Check if the value on the item is not Null. Access the value. IF ( xml_object. existsNode(/ROOT_TAG/HOTLINE) = 1 ) THEN IF( xml_object. extract(/ROOT_TAG/HOTLINE/text()) IS NOT NULL ) THEN xml_data_str := xml_object. xtract(/ROOT_TAG/HOTLINE/text()). getStringval(); END IF; END IF; The xml_object. existsNode just checks whether the element specified in the Xpath exists. If so returns Scalar 1. Then the code checks whether the element is null or not, this step may looks like redundant but this is needed for a cleaner code. Then the . getStringval() returns the String value. In the best case only the 3rd step in enough but†¦ all the 1st and 2nd lines for a more dependab le code. 4. Accessing reparative set of Items and fetching the values. Point here to note is you can access the specific Item of a repetitive list of items using an index. Index starts at 1†¦. xml_data_str := xml_object. extract(/ROOT_TAG/ITEM[1]/CHANNEL/text()). getStringval() ; xml_data_str := xml_object. extract(/ROOT_TAG/ITEM[2]/CHANNEL/text()). getStringval() ; xml_data_str := xml_object. extract(/ROOT_TAG/ITEM[3]/CHANNEL/text()). getStringval() ;The above code will return the channel of the first Item,second,third. U can always write a loop by incrementing the Index vale and access all the item. 5. Checking if a Specific Item with a specific vale exists. IF ( xml_object. existsNode(/ROOT_TAG/ITEM[CHANNEL=PRINTER]) = 1 ) THEN †¦. END IF:Above code only checks whether I item with the channel == â€Å"PRINTER† exists†¦ simple. these â€Å". existsNode† checks can be very useful when you are searching a value of a frequently changing XML. if other methods such as â€Å"extract† hits a null element you will endup with an exception, so always use â€Å"existNode† to before accessing the values. this is a very basic. but very frequently used set of xml extraction stuff. read the doc people..