XML Parsing help

VTEST · #1 (**permalink**) 01-17-12, 02:35

Hi,

I need to parse the following XML string. This must be done in function. which get ref_cursor as OUT parameter and the XML string as VARCHAR or CLOB.

Input XML :

<Employees>
<EmplList>
<name>XXXX</name>
<Roll>1001</Roll>
<sal>100.50</sal>
</EmplList>
<EmplList>
<name>YYYY</name>
<Roll>1002</Roll>
<sal>200.50</sal>
</EmplList>
<EmplList>
<name>ZZZZ</name>
<Roll>1003</Roll>
<sal>300.50</sal>
</EmplList>
</Employees>

XML string must be parsed and returned result cursor as if the following way:

Name SSN Sal
-----------------------------
XXXX 1001 100.50
YYYY 1002 200.50
ZZZZ 1003 300.50

Please help me to write a function to get it done...

Thanks in advance.

flyboy · #2 (**permalink**) 01-17-12, 03:02

Hi,
if you use at least Oracle 10gR2 (it would be nice if you posted your Oracle version), you may use XMLTABLE function for this:

Code:

select name, roll, sal
from xmltable('/Employees/EmplList'
        passing xmltype( <your_string_content> )
        columns 
          name varchar2(4) path '/EmplList/name',
          roll integer path '/EmplList/Roll',
          sal number(4,1) path '/EmplList/sal' );

I set data types based on your sample data - you shall adjust them according the real XML content.

For the detailed explanation, read its description in SQL Language Reference book. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

VTEST · #3 (**permalink**) 01-17-12, 07:10

Thank you very much to you...

Our DB version is:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

I need one more clarification that i want to send this whole XML as string from my application. Can you tell me what data type may i use to receive this XML string input in function.

Internet Services

Web Development

Digital Marketing

Consumer Tech