user5005768 user5005768 - 3 months ago 12
SQL Question

Oracle SQL: how to pass sysdate in path of XMLTable

I am retrieving data from xml for insert into table.but i also want to insert an additional data sysdate in to following xmltable table:

sample XML data:
<employee_info>
<employee_id>ID1234</employee_id>
<name>John</name>
</employee_info>

-- This is the query Notice at CREATED_ON :

INSERT INTO TBL_EMPLOYEE TBL (TBL.EMP_ID, TBL.Name, TBL.Created_on)
SELECT x.* FROM XMLTABLE('/employee_info'
PASSING xmlData
COLUMNS EMP_ID VARCHAR2(10) PATH 'employee_id',
Name VARCHAR2(50) PATH 'name',
CREATED_ON DATE PATH '<created_on>sysdate</created_on>');


How can i pass the
sysdate
on collumn
CREATED_ON
that is not part of the given xml sample.please help.thanks

Answer

You don't need to operate on xml level to have column with sysdate:

INSERT INTO tbl_employee tbl
     (tbl.emp_id
     ,tbl.name
     ,tbl.created_on)
SELECT t.emp_id, t.name, sysdate
  FROM xmltable('/employee_info' 
                passing xmldata 
                columns 
                        emp_id VARCHAR2(10) path 'employee_id', 
                        NAME VARCHAR2(50) path 'name'
               ) t;