CODEWITHSUNDEEP
sqloracle-database
Usha S
Usha S

Reputation: 1

SQL Query for XML Type

SELECT   employeeName
  FROM   XMLTABLE (xmlnamespaces (
                      'http://urn:global:cs:common' AS "tns0") , '/tns0:organization/tns0:employee' PASSING XMLType 
                      ((select a.EmployeeXML from impp.Employee a))
                      COLUMNS
                      employeeName NUMBER PATH 'tns0:EmployeeDetails/@value'
                       ) xmlT;

It shows an exception

ORA-01427: single-row subquery returns more than one row.

Upvotes: 0

Views: 104

Answers (1)

peter.hrasko.sk
peter.hrasko.sk

Reputation: 4141

Oracle has this nice feature of doing a lateral-view join when you write a regular cartesian product to an xmltable(...).

Try it this way:

SELECT xmlT.employeeName
FROM impp.Employee A
    XMLTABLE(
        xmlnamespaces('http://urn:global:cs:common' AS "tns0"),
        '/tns0:organization/tns0:employee'
        PASSING XMLType(A.EmployeeXML)
        COLUMNS
            employeeName NUMBER PATH 'tns0:EmployeeDetails/@value'
    ) xmlT;

Notes:

  • It appears that your impp.Employee.EmployeeXML is a CLOB column. That's why this code will work OK. If the EmployeeXML is an XmlType column instead, just leave the "typecast" to XmlType out of the query.

And since you're new to stackoverflow.com...

  • If the code is not working, comment on the resulting errors and I'll correct it.
  • If the code is working, don't forget to mark the answer as accepted by clicking on the check mark.

Upvotes: 1

Related Questions