Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Parse data from XML

Hi
DB version 11.2.0.1
I have clob with name "a" in table dummy_clob which include XML date, i need to select this data but there is error apper ORA-19228: XPST0008
Query:
SELECT x.Cba_Number,x.Reference_Id FROM dummy_clob t, XMLTABLE ( '/ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1"' PASSING xmltype (t.a) COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber', Reference_Id VARCHAR2 (2000) PATH 'ReferenceId') x
XML:
<ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1"> <CbaNumber>29291618</CbaNumber> <ReferenceId>123459</ReferenceId> </ns2:SearchResponseType>
Best Answer
-
You have to add the namespace declarations to the xmltable function at first:
SELECT x.Cba_Number,x.Reference_Id
FROM t,
XMLTABLE (
xmlnamespaces ('##default' as "ns2", default 'urn:com.xxx:xxx:xxx:1')
,'/ns2:SearchResponseType'
PASSING xmltype (t.a)
COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',
Reference_Id VARCHAR2 (2000) PATH 'ReferenceId'
) x
Answers
-
You have to use XMLNAMESPACES clause of XMLTABLE:
WITH dummy_clob AS (SELECT '<ns2:SearchResponseType xmlns:ns2="##default" xmlns="urn:com.xxx:xxx:xxx:1">
<CbaNumber>29291618</CbaNumber>
<ReferenceId>123459</ReferenceId>
</ns2:SearchResponseType>' a from dual)
SELECT x.Cba_Number,x.Reference_Id
FROM dummy_clob t,
XMLTABLE (
XMLNAMESPACES('##default' as "ns2",
default 'urn:com.xxx:xxx:xxx:1'
),
'/ns2:SearchResponseType'
PASSING xmltype (t.a)
COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',
Reference_Id VARCHAR2 (2000) PATH 'ReferenceId') x
/
CBA_NUMBER REFERENCE_ID
---------- ------------
29291618 123459SQL>
SY.
-
You have to add the namespace declarations to the xmltable function at first:
SELECT x.Cba_Number,x.Reference_Id
FROM t,
XMLTABLE (
xmlnamespaces ('##default' as "ns2", default 'urn:com.xxx:xxx:xxx:1')
,'/ns2:SearchResponseType'
PASSING xmltype (t.a)
COLUMNS Cba_Number VARCHAR2 (2000) PATH 'CbaNumber',
Reference_Id VARCHAR2 (2000) PATH 'ReferenceId'
) x