This code is really similar to the one i wrote at Corvex for the AuditXML export functionality.

The situation is the same as there. You generate an XML with the usual XmlElement, Xmlconcat,XmlForest method.

But you have to write it to a clob field. We use Delphi 6 there and it doesn’t know about Oracle’s XMLtype field, so yes raw clob. Clob fields store XML data perfectly , but your XML looses its nice format like

1
2
3
4
5
6
7
<Human>

<Name>John Doe </Human>

<Title>Dr</Title>

</Human>

It becomes a row of raw and  pure XML data. What if you have to restore the formatting. I came up with this solution:

Regex Warning!!

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
SET DEFINE OFF;
CREATE OR REPLACE PROCEDURE FormatAndWriteXMLToFile
IS

xml_clob CLOB;
buffered varchar2(27000);
buffer_size BINARY_INTEGER := 27000;
processed varchar2(32000);
amount BINARY_INTEGER;
last_char Char; -- Last char of the previous chunk of the clob.
last_tag varchar2(600); -- Last Partial or full tag of the last chunk.
offset NUMBER(38);
x NUMBER;
len Number;
beg_pos pls_integer;

file_handle UTL_FILE.FILE_TYPE;
directory_name CONSTANT VARCHAR2(80) := 'MY_DIR';
new_xml_filename CONSTANT VARCHAR2(80) := 'exportedXML'||Sysdate||'.xml';

BEGIN

DBMS_OUTPUT.ENABLE(100000);
-- ----------------
-- GET CLOB LENGTH
-- ----------------
SELECT dbms_lob.getlength(sor) INTO len
FROM xmlstoringclobtable;
-- ----------------
-- GET CLOB
-- ----------------
SELECT sor INTO xml_clob
FROM xmlstoringclobtable;
-- --------------
-- save blob length
-- ------------
x :=len;

-- --------------------------------
-- OPEN NEW XML FILE IN BINARY WRITE MODE
-- --------------------------------
file_handle := UTL_FILE.FOPEN(directory_name, new_xml_filename,'wb');
-- utl_file.PUT(file_handle,'OPENED');

amount := buffer_size-600;
offset := 1;

-- ----------------------------------------------
-- READ FROM CLOB XML / WRITE OUT NEW XML TO DISK
-- ----------------------------------------------
UTL_FILE.put_raw(file_handle,UTL_RAW.cast_to_raw('<?xml version="1.0" encoding="ISO-8859-2"?>'||CHR(13)||'<Adatok xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">'));-- Write XML DEFINITION
WHILE offset < len and buffer_size > 0
LOOP
DBMS_LOB.READ(xml_clob,amount,offset,buffered);
buffered:=last_tag||buffered;
--beg_pos:=INSTR(buffered,'</',-1,1);
buffered:=REPLACE(buffered,'&apos', CHR(39));
buffered:=REPLACE(buffered,'"',CHR(34));
buffered:=REPLACE(buffered,'&','&');
beg_pos:=INSTR(SUBSTR(buffered,0,INSTR(buffered,'</',-1,1)-1),'<',-1,1);
last_tag:=SUBSTR(buffered,beg_pos,length(buffered));
buffered:=substr(buffered,0,beg_pos-1);
processed:=REGEXP_REPLACE(buffered,'(\<[^\/])',chr(10)||'\1'); --REGEX MAGIC ( Replace < with \n< if not </)

processed:=REPLACE(processed,'><','>'||chr(10)||'<');--Break lines.
processed:=REGEXP_REPLACE(processed,'<(\w+)>\s*<\/(\w+)>','<\1 />'); --REGEX MAGIC(Eliminate empty XML TAGS
if(last_char='>' and substr(processed,0,1)='<') then
processed:=chr(10)||processed;
end if;
offset := offset + amount;
last_char:=SUBSTR(processed,-1);
UTL_FILE.PUT_RAW(file_handle,utl_raw.cast_to_raw(processed));--Write Processed Chunk to file in RAW mode
UTL_FILE.FFLUSH(file_handle);
-- set the end position if less than 27000 bytes
x := x - (buffer_size-600);
IF x < 26400 THEN
buffer_size := x;
END IF;
END LOOP;
if(last_char='>' and substr(last_tag,0,1)='<') then
last_tag:=chr(13)||last_tag;
end if;
last_tag:=REGEXP_REPLACE(last_tag,'(\<[^\/])',chr(10)||'\1'); --REGEX MAGIC ( Replace < with \n< if not </)
last_tag:=REPLACE(last_tag,'><','>'||chr(10)||'<');--Break lines.
last_tag:=REGEXP_REPLACE(last_tag,'<(\w+)>\s*<\/(\w+)>','<\1 />'); --Eliminate Empty XML tags
UTL_FILE.PUT_RAW(file_handle,utl_raw.cast_to_raw(last_tag));
UTL_FILE.PUT_RAW(file_handle,utl_raw.cast_to_raw(chr(10)||'</Adatok>'));
UTL_FILE.FCLOSE(file_handle);

END;
/

OpenSo(u)rcery

Long time, no see. Don't worry, I'm not dead, not even remotely dead. I'm just pretty busy. I'm actually working on great stuff at work and …… Continue reading

Openduty @ Cloud Budapest

Published on April 12, 2015

Leaving Ustream

Published on February 07, 2015