CODEWITHSUNDEEP
sqlxmloracle-database
user1630575
user1630575

Reputation: 171

Convert XML Type to VARCHAR

I am trying to convert the data extracted from XMLTYPE column to varchar but it gives me error:

SELECT TO_CHAR(EXTRACT (XML_DATA, '/root/MYData/text()'))
  FROM TBL_DATA

Gives error "Inconsistent datatypes: expected NUMBER got - "

My motive is to put the extracted value into a variable and use it further in stored proc and the node can contain number or alphabets:

SELECT TO_CHAR(EXTRACT (XML_DATA, '/root/MYData/text()')) INTO TEMP_VAR
  FROM TBL_DATA

Upvotes: 0

Views: 8394

Answers (1)

MT0
MT0

Reputation: 168212

Use EXTRACTVALUE rather than EXTRACT:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TBL_DATA ( XML_DATA ) AS
SELECT XMLTYPE( '<root><MYData>test string</MYData></root>' ) FROM DUAL;

Query 1:

SELECT EXTRACTVALUE(XML_DATA, '/root/MYData/text()')
  FROM TBL_DATA

Results:

| EXTRACTVALUE(XML_DATA,'/ROOT/MYDATA/TEXT()') |
|----------------------------------------------|
|                                  test string |

Query 2:

However, EXTRACT and EXTRACTVALUE are deprecated and oracle recommends using XMLTABLE instead:

SELECT value
FROM   TBL_DATA t
       CROSS JOIN
       XMLTABLE(
         '/root/MYData'
         PASSING t.XML_DATA
         COLUMNS value VARCHAR2(200) PATH 'text()'
       )

Results:

|       VALUE |
|-------------|
| test string |

Upvotes: 1

Related Questions