XMLDB - 節點到行

我有一個將 XML 作為 XMLType 儲存在表中的應用程序。這是我第一次使用 XMLDB 和 XMLType 特性，我想知道是否可以以“關係”方式使用 XQuery FLWOR 表達式的結果。

考慮以下範例 XML：

<PerfInfo TimeStamp="2014-11-23 17:13:04" GUID="5d4a9a3f-5a1c-478c-b12f-39e9f33172f4">
   <EventLog Count="408">
       <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:31" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
       <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-17 00:18:43" Provider="Application Error" ID="1000">Faulting application name: wmiprvse.exe</EventRecord>
       <EventRecord LogName="Application" Level="2" TimeCreated="2014-11-18 16:59:28" Provider="McLogEvent" ID="5051">A thread in process C:\Program Files\Common Files\McAfee\SystemCore\mcshield.exe took longer than 90000 ms to complete </EventRecord>
   <EventLog/>
<PerfInfo/>

您可能會注意到這些是從 Windows 事件日誌中提取的。

這些結果（實際的 XML 文件當然要大得多，並且包含許多其他指標，但原理保持不變）儲存在下表中：

CREATE TABLE SPERF_XML_RAW 
(
   "ID"                NUMBER              NOT NULL, 
   "GUID"              CHAR(36 CHAR)       NOT NULL,
   "HOSTNAME"          VARCHAR2(16 CHAR)   NOT NULL,
   "USERNAME"          VARCHAR2(16 CHAR)   NOT NULL,
   "DATA_XML"          PUBLIC.XMLTYPE      NOT NULL,
   "DATE_CREATED"      TIMESTAMP(6)        NOT NULL,
   "DATE_INSERTED"     TIMESTAMP(6)        DEFAULT SYSDATE NOT NULL,
   CONSTRAINT SPERF_XML_RAW_PK PRIMARY KEY(ID)
)

例如，我可以使用 XQuery 查詢不同 XML 文件中事件記錄的所有提供者名稱，如下所示：

SELECT ID, GUID, 
  XMLQuery(
      'for $i in /PerfInfo/EventLog/EventRecord
      return $i/@Provider'
      PASSING DATA_XML RETURNING CONTENT
  ) "DATA_THINGS"
FROM SPERF_XML_RAW;

結果將是這樣的：

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application ErrorApplication ErrorMcLogEvent

但是，我想要的結果是這樣的：

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent

或者，在查詢多個文件時：

1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    Application Error
1   5d4a9a3f-5a1c-478c-b12f-39e9f33172f4    McLogEvent  
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    ESENT
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    Microsoft-Windows-Search
2   9784fcc3-ee7d-49a0-bf5d-4ac4fe923fc9    McLogEvent
3   cd1054b1-1fe0-4842-a9ed-3f1b3c09ea0f    Application Hang
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Group Policy
4   6c95827b-8fba-42d7-b34e-fec0b9e9606a    Microsoft-Restart-Manager

換句話說，我希望能夠以這樣一種方式查詢我的 XML 文件，即每個選定的節點都作為一個行返回。有沒有辦法使用 XMLDB 和 Oracle 11.2g 來實現這一點？

**PS：**在實際查詢中，我當然會檢索的不僅僅是提供者，這只是一個範例查詢。我希望在文件中建構不同的視圖，以探索文件中包含的不同指標

在閱讀了 Oracle 的大量文件後，我自己找到了答案。答案分為兩部分。

首先，我原來的問題可以通過使用 XMLTable 來解決：

SELECT 
 x.ID            AS "MEASUREMENT_ID", 
 x.GUID          AS "MEASUREMENT_GUID", 
 a.COLUMN_VALUE  AS "MESSAGE"
FROM SPERF_XML_RAW x,
 XMLTable('/PerfInfo/EventLog/EventRecord/@Provider'
           PASSING x.DATA_XML) a;

請注意，COLUMN_VALUE 是一個偽列，可以與 XMLTable 結合使用

作為附加資訊，我想添加以下內容。您可以使用以下語法創建多個列：

SELECT 
   x.ID,
   x.GUID,
   res."Provider", 
   res."TimeCreated"
FROM SPERF_XML_RAW x,
   XMLTABLE('/PerfInfo'
     PASSING x.DATA_XML
     COLUMNS 
        "Provider"     VARCHAR2(32 CHAR)   PATH '/PerfInfo/EventLog/EventRecord',
        "TimeCreated"  TIMESTAMP           PATH '/PerfInfo/EventLog/EventRecord') res;

但是當一列或多列返回多個節點時，Oracle 會拋出 ORA-19279: XPTY0004。

ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 -  "XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence" 
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.

可以在下面找到該問題的解決方案：

SELECT
 x.ID              AS "MEASUREMENT_ID",
 x.GUID            AS "MEASUREMENT_GUID",
 res."TIMECREATED", 
 res."LOGNAME",
 res."LEVEL",
 res."ID",
 res."PROVIDER",
 res."MESSAGE"
FROM SPERF_XML_RAW x,
  XMLTable('/PerfInfo' 
     PASSING x.DATA_XML
     COLUMNS
       "EventRecords" XMLTYPE PATH 'EventLog') rec,
  XMLTABLE ('EventLog/EventRecord'
      PASSING rec."EventRecords"
      COLUMNS
       "LOGNAME"         VARCHAR(32 CHAR)      PATH '@LogName',
       "ID"              NUMBER                PATH '@ID',
       "LEVEL"           CHAR(1 CHAR)          PATH '@Level',
       "PROVIDER"        VARCHAR2(128 CHAR)    PATH '@Provider',
       "TIMECREATED"     VARCHAR2(32 CHAR)     PATH '@TimeCreated',
       "MESSAGE"         CLOB                  PATH 'text()') res;

一旦您的文件有太多級別，這可能會再次失敗，並且我沒有包含具有名稱空間的文件，這很可能也會導致爆炸。

所有資訊均在 XML DB 開發人員指南中找到：https ://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369

引用自：https://dba.stackexchange.com/questions/83418