Tuesday, 9 December 2014

How to import complex XML Types in Oracle SQL using XMLTABLE



I have the following XML Files completely imported in the table called ARCHIVDATA into one single cell called CLOB_CONTENT:



<AuditLog>
<AuditLogEntry>
<Header>1
</Header>
<Content>2
</Content>
</AuditLogEntry>
<AuditLogEntry>
<Header>3
</Header>
</AuditLogEntry>
<AuditLogEntry>
<Header>4
</Header>
<Content>5
</Content>
<Content>6
</Content>
</AuditLogEntry>
</AuditLog>


What I want as a result is the following table (table with two rows):

Header | Content
1 | 2

3 | NULL

4 | 5

4 | 6



How can I do that?


I already tried the following:



SELECT x3.header, x4.content
FROM (select xmltype(xml.CLOB_CONTENT) xmldata from ARCHIVDATA) x1,
xmltable('/AuditLog/AuditLogEntry'
passing x1.xmldata
columns
header XmlType path 'header',
content XmlType path 'content'
)x2,
xmltable('/header'
passing x2.header
columns
header varchar2(4000) path '.'
)x3,
xmltable('/content'
passing x2.content
columns
content varchar2(4000) path '.'
)x4
;/


What do I have to change to get the desired result table?


No comments:

Post a Comment