如何使用XMLTABLE在Oracle SQL中导入复杂的XML类型

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

我将名为ARCHIVDATA的表中完全导入的以下XML文件放入一个名为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?

我想要的结果是下表（有两行的表）：Header |内容1 | 2 3 | NULL 4 | 5 4 | 6我该怎么做？

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?

我需要更改什么才能获得所需的结果表？

1 个解决方案

#1

I solved this task with next query. But I am sure that more easier way exists.

我用下一个查询解决了这个任务。但我相信存在更简单的方法。

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;