XQuery select and check existing employee with message

Question

i need to output a message saying "matched" or "not an employee" in a column using xquery. I'm using 4444 as a placeholder for now, dont know how to iterate through within the query

SELECT
EXTRACTVALUE(B.JOBSHEET_DETAILS, '/jobSheet[@jobNo]/@jobNo') AS "JOB_DETAILS_ID",
XMLQUERY(' for $j in /jobSheet/employee[@empNo]/@empNo
        let $msg := if ($j) then "Employee details match a current employee" else "Error in employee details (no match)"
        where $j = 4444
        return $msg
        '
        PASSING B.JOBSHEET_DETAILS
        RETURNING CONTENT ).getstringval() AS "CHECK_EMP_DETAILS" ,
EXTRACTVALUE(B.JOBSHEET_DETAILS, '/jobSheet/employee[@empNo]/@empNo') AS "EMP_NO",
EXTRACTVALUE(B.JOBSHEET_DETAILS, '/jobSheet/employee/empName') AS "EMP_NAME",
EXTRACTVALUE(B.JOBSHEET_DETAILS, '/jobSheet/dateAttend/date') AS "DATE_ATTENDED"
FROM JOBSHEET_XML_TAB B
where XMLEXISTS('/jobSheet/employee[@empNo]/@empNo' passing B.JOBSHEET_DETAILS);

for one, the else inside xmlquery doesnt trigger. it outputs (null) when it should be an error.

I need to check all employees if the $j in this iterations is an existing employee or not. $j = 4444 is it possible to do another select count(*)from emp where no = $num.

Answer 1

I wouldn't try to do that in the XPath at all, if it's even possible. You can use XMLQuery() or XMLTable() to extract the element and attribute values you're interested in, and then outer-join to the emp table to check if they exist, with a case expression to decide what text to display.

Based on what your XML structure appears to be from your current code, something like:

select x.job_details_id,
  case when e.no is not null then 'Employee details match a current employee'
    else 'Error in employee details (no match)' end as check_emp_details,
  x.emp_no,
  x.emp_name,
  x.date_attended
from jobsheet_xml_tab jxt
cross join XMLTable(
  '/jobSheet/employee[@empNo]'
  passing jxt.jobsheet_details
  columns job_details_id number path './../@jobNo',
    emp_no number path './@empNo',
    emp_name varchar2(20) path './empName',
    date_attended date path './../dateAttend/date'
) x
left join emp e on e.no = x.emp_no;

Quick demo with made-up XML and emp record in CTEs:

with jobsheet_xml_tab(jobsheet_details) as (
  select xmltype('<jobSheet jobNo="666666">
 <dateAttend>
  <date>2017-05-01</date>
 </dateAttend>
 <employee empNo="7777">
  <empName>Joe</empName>
 </employee>
</jobSheet>') from dual
  union all
  select xmltype('<jobSheet jobNo="777777">
 <dateAttend>
  <date>2017-05-03</date>
 </dateAttend>
 <employee empNo="4444">
  <empName>Jane</empName>
 </employee>
</jobSheet>') from dual
),
emp (no) as (
  select 4444 from dual
)
select x.job_details_id,
  case when e.no is not null then 'Employee details match a current employee'
    else 'Error in employee details (no match)' end as check_emp_details,
  x.emp_no,
  x.emp_name,
  x.date_attended
from jobsheet_xml_tab jxt
cross join XMLTable(
  '/jobSheet/employee[@empNo]'
  passing jxt.jobsheet_details
  columns job_details_id number path './../@jobNo',
    emp_no number path './@empNo',
    emp_name varchar2(20) path './empName',
    date_attended date path './../dateAttend/date'
) x
left join emp e on e.no = x.emp_no;

JOB_DETAILS_ID CHECK_EMP_DETAILS                             EMP_NO EMP_NAME             DATE_ATTE
-------------- ----------------------------------------- ---------- -------------------- ---------
        666666 Error in employee details (no match)            7777 Joe                  01-MAY-17
        777777 Employee details match a current employee       4444 Jane                 03-MAY-17

It probably isn't useful, but in principle this would cope with a job that had multiple employees assigned to it, which using the deprecated extractvalue() could not.