Hi Gurus,
Require your help in solving my issue.
My requirement is to read an XML file and extract data from certain nodes .
Transpose some of the data (rows to columns) and store it in a table.
For this I followed the following approach
1)Extract the data in filedata nodes
2)If a "dir" node has a "file" node then extract only those "dir" nodes
3)For "verinfo" node make the value of the attribute name as column name and print the value of the attribute "value" as the value
I need to display as below
Dir_Name Dir_Date Dir_Attrib File_name File_Attr EXE_Type Date_Modified Date_Accessed Date_Created
C:\ADOBE\ACROBAT 2014-09-23 17:57:46 XA Win 32 Exe Xa Win 32 Exe 2014-09-23 17:50:53 2014-09-23 17:50:53 2014-09-23 17:50:53
File_Size Dos8.3_Name Company_Name
442368 INS~1.EXE Adobe Systems
LOADFILE. XML
----------------
<?xml version="1.0" encoding="UTF-8"?>
<filedata>
<dir name="C:\"/>
<dir date="2015-09-23 08:27:01" name="C:\TEST"/>
<dir date="2015-01-16 11:22:56" name="C:\ADOBE"/>
<dir date="2014-09-23 17:57:46" name="C:\ADOBE\ACROBAT" attr="XA" >
<file name="install.exe" attr="Xa" exetype="Win 32 Exe" modified="2014-09-23 17:50:53" accessed="2014-09-23 17:50:53" created="2014-09-23 17:50:53" size="442368">
<verinfo value="INS~1.EXE" name="DOS 8.3 Name"/>
<verinfo value="Adobe Systems" name="Company Name"/>
</file>
LOAD.CTL
-------------
LOAD DATA
INFILE 'LOADFILE.XML'
INTO TABLE TH_XML TRUNCATE
(IN_FILE_1 TERMINATED BY EOF ,COL_ID_1 )
BEGINDATA MYID
CREATE TABLE TH_XML
(
COL_ID_1 VARCHAR2(100 BYTE),
IN_FILE_1 XMLTYPE
);
SQL QUERY
-----------------------
select
extractValue(value(x),'/dir/@name') dir_name
, extractValue(value(y),'/file/@name') file_name
,extractValue(value(z),'/verinfo/@name') version_name
FROM th_xml
, TABLE (XMLSequence(extract(in_file_1,'/inventory/filedata/dir'))) x
, TABLE (XMLSequence(extract(in_file_1,'/inventory/filedata/dir/file'))) y
, TABLE (XMLSequence(extract(in_file_1,'/inventory/filedata/dir/file/verinfo'))) z
where existsNode(x.object_value,'/dir/file')=1
;
But it is not working
Thanks in Advance