How to import complex XML types into Oracle SQL using XMLTABLE
I have the following XML files fully imported into the ARCHIVDATA table in one CLOB_CONTENT cell:
<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>
As a result, I want the following table (table with two rows):
Title | Contents
1 | 2
3 | NULL
4 | 5
4 | 6
How can I do this?
I've 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 need to change to get the desired results table?
+3
source to share
1 answer
I solved this problem with the following query. But I'm sure there is an easier way.
SELECT headers.header,
contents.content
FROM
(SELECT extracted."Header" AS header
FROM ARCHIVDATA a,
xmltable('/AuditLog/AuditLogEntry' passing xmltype(a.CLOB_CONTENT) columns "Header" VARCHAR2(6) PATH '/AuditLogEntry/Header') extracted
) headers
LEFT JOIN
(SELECT sel.Header,
sel2.content
FROM
(SELECT extracted."Header" AS header,
extracted."Content" AS content
FROM ARCHIVDATA a,
xmltable('/AuditLog/AuditLogEntry' passing xmltype(a.CLOB_CONTENT) columns "Header" VARCHAR2(6) PATH '/AuditLogEntry/Header', "Content" xmltype path '/AuditLogEntry') extracted
) sel,
xmltable('/AuditLogEntry/Content' passing sel.content columns content VARCHAR2(6) path '/Content') sel2
) contents
ON headers.header = contents.header;
0
source to share