1 Reply Latest reply: Jan 2, 2013 5:28 PM by Andreas Weiden RSS

    Querying node value(s) from XML insde a varchar2 column

    430630
      CREATE TABLE "DEV1"."LOG" (
      "LOG_ID" NUMBER(10,0) NOT NULL,
      "LOG_DATA" VARCHAR2(1000 BYTE) NULL)

      SAMPLE LOG_DATA COLUMN DATA:
      <Log>
      <PartNumbers>13000,15000</PartNumbers>
      <OrderID>221</OrderID>
      <OrderDateTime>01/01/2013 22:32:43</OrderDateTime>
      <UserName>testUser</UserName>
      <ZipCode>12345</ZipCode>
      <TrackingID>333444555</TrackingID>
      </Log>     

      SELECT x.OrderID
      FROM DEV1.LOG
      ,xmltable('/Log'
      passing LOG.LOG_DATA
      columns OrderID char(100)     path './OrderID'
      ) x
                          
      ERROR
      ORA-00932: inconsistent datatypes: expected - got CHAR

      No matter what datatype I put after "columns OrderID" I get this error. How can I get the data?