This discussion is archived
1 Reply Latest reply: Jan 2, 2013 3:28 PM by Andreas Weiden RSS

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

430630 Newbie
Currently Being Moderated
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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points