I found the answer myself after reading through loads of documentation from Oracle. The answer comes in two parts.
First, my original question can be solved by using 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;
Note that COLUMN_VALUE is a pseudocolumn that can be used in conjunction with XMLTable
As additional information I would like to add the following. You can create multiple columns with the following syntax:
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;
But Oracle will throw ORA-19279: XPTY0004 when one or more columns return multiple nodes.
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.
The solution to THAT problem can be found below:
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;
This will probably fail once more as soon as your document has too many levels, and I have not included a document with namespaces, which will most likely cause this to explode as well.
All information was found in the XML DB Developers guide: https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb03usg.htm#g1055369
Best Answer
Increasing the size of a table will always have a negative impact on performance. So adding XML columns will have a negative impact.
Splitting the tables will result in more flexibility as you will not always shuffle between the xmls.
That depends on your need to browse and use the xmls on the server side. SQL does not keep the xml in the database as a string (nvarchar) but in an optimized form. If you do not need to use it as it is in tsql then you will not need to keep it as an XML datatype so that means you can compress it as you want and keep it even binary.