Sql-server – SQL Server xml performance

There is a comparable issue logged on connect for the version you're having a problem with - An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1.

Using SQL2008, when shredding XML using the nodes query, the performance is fine when just SELECTing. However, when you want to INSERT the data to a table/temptable/tablevariable the performance becomes very poor.

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