I'm trying to pull values from the XML below, stored in xml_table as XMLType, using Oracle SQL.
select x.*
from xml_table t
, xmltable( XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'urn:com.workday/bsvc' as "wd"
), 'for $i in //wd:Get_Worker_Costing_Allocations_Response return $i'
--,'env:Envelope/env:Body/wd:Get_Worker_Costing_Allocations_Response/wd:Response_Data/wd:Worker_Costing_Allocations_Data'
passing t.raw_xml
columns Employee_ID number path 'wd:Response_Data/wd:Worker_Costing_Allocations_Data/wd:Worker_Reference/wd:ID[2]'
,Position_ID varchar2(100) path 'wd:Response_Data/wd:Worker_Costing_Allocations_Data/wd:Position_Reference/wd:ID[2]'
--,start_Date date path 'wd:Response_Data/wd:Worker_Costing_Allocations_Data/wd:Worker_Costing_Allocation_Interval_Data/wd:Start_Date'
) x
When i tried to get the fund id and other details like Cost_Center_Reference_ID could not get from Worker_Costing_Allocation_Interval_Data. Ultimately i have to loop through all Worker_Costing_Allocation_Interval_Data nodes.
select x.*
from xml_table t
, xmltable( XMLNAMESPACES (
'http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'urn:com.workday/bsvc' as "wd"
), 'for $i in //wd:Worker_Costing_Allocation_Detail_Data return $i'
--,'env:Envelope/env:Body/wd:Get_Worker_Costing_Allocations_Response/wd:Response_Data/wd:Worker_Costing_Allocations_Data'
passing t.raw_xml
columns order_1 varchar2(20) path 'wd:Order'
,fund_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@Fund_ID]'
) x
Expected Output:
employee_id Position_Id Start_Date End_Date Order Fund_ID Project_ID Program_ID Cost_Center_Reference_ID Custom_Organization_Reference_ID Distribution_Percent
11111 P11111 7/1/2018 6/30/2050 a FD89 PJ122201 PG11130 CC12340 0.4
11111 P11111 7/1/2018 6/30/2050 b FD89 PJ122201 PG11130 CC1234 0.6
11111 P11111 7/1/2017 6/30/2018 a FD30 PJ000001 PG00003 CC0565 UD000004 1
11111 P11111 1/1/2017 6/30/2017 a FD30 PJ000001 PG00003 CC0565 UD000004 1
Below is the raw XML inserted to xml_table:
<?xml version='1.0' encoding='UTF-8'?>
<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
<env:Body>
<wd:Get_Worker_Costing_Allocations_Response xmlns:wd="urn:com.workday/bsvc" wd:version="v15">
<wd:Request_Criteria>
<wd:Costing_Override_Criteria>
<wd:Worker_Reference wd:Descriptor="First, Last">
<wd:ID wd:type="WID">WID1234</wd:ID>
<wd:ID wd:type="Employee_ID">11111</wd:ID>
</wd:Worker_Reference>
</wd:Costing_Override_Criteria>
</wd:Request_Criteria>
<wd:Response_Group>
<wd:Exclude_Allocation_Detail_Data>0</wd:Exclude_Allocation_Detail_Data>
</wd:Response_Group>
<wd:Response_Results>
<wd:Total_Results>1</wd:Total_Results>
<wd:Total_Pages>1</wd:Total_Pages>
<wd:Page_Results>1</wd:Page_Results>
<wd:Page>1</wd:Page>
</wd:Response_Results>
<wd:Response_Data>
<wd:Worker_Costing_Allocations_Data>
<wd:Worker_Reference wd:Descriptor="First, Last">
<wd:ID wd:type="WID">WID1234</wd:ID>
<wd:ID wd:type="Employee_ID">11111</wd:ID>
</wd:Worker_Reference>
<wd:Position_Reference wd:Descriptor="P11111 Student - First, Last.">
<wd:ID wd:type="WID">WID2345</wd:ID>
<wd:ID wd:type="Position_ID">P11111</wd:ID>
</wd:Position_Reference>
<wd:Worker_Costing_Allocation_Interval_Data>
<wd:Costing_Override_ID>COSTING_OVERRIDE-X-ZZZZ</wd:Costing_Override_ID>
<wd:Start_Date>2018-07-01-07:00</wd:Start_Date>
<wd:End_Date>2050-06-30-07:00</wd:End_Date>
<wd:Worker_Costing_Allocation_Detail_Data>
<wd:Order>a</wd:Order>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="FD89 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Fund_ID">FD89</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PJ122201 XXXXX YYYYY">
<wd:ID wd:type="WID">d18edc6167911037c23bcdd377765bf3</wd:ID>
<wd:ID wd:type="Project_ID">PJ122201</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PG11130 XXXXX YYYYY">
<wd:ID wd:type="WID">d18edc61679110372db25f84888138fc</wd:ID>
<wd:ID wd:type="Program_ID">PG11130</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="CC12340 XXXXX YYYYY">
<wd:ID wd:type="WID">d18edc61679110376fe5ad0c91c3805f</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">CC12340</wd:ID>
<wd:ID wd:type="Cost_Center_Reference_ID">CC12340</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Distribution_Percent>0.4</wd:Distribution_Percent>
</wd:Worker_Costing_Allocation_Detail_Data>
<wd:Worker_Costing_Allocation_Detail_Data>
<wd:Order>b</wd:Order>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="CC1234 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">CC1234</wd:ID>
<wd:ID wd:type="Cost_Center_Reference_ID">CC1234</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="FD89 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Fund_ID">FD89</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PJ122201 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Project_ID">PJ122201</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PG11130 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Program_ID">PG11130</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Distribution_Percent>0.6</wd:Distribution_Percent>
</wd:Worker_Costing_Allocation_Detail_Data>
</wd:Worker_Costing_Allocation_Interval_Data>
<wd:Worker_Costing_Allocation_Interval_Data>
<wd:Costing_Override_ID>COSTING_OVERRIDE-X-YYYY</wd:Costing_Override_ID>
<wd:Start_Date>2017-07-01-07:00</wd:Start_Date>
<wd:End_Date>2018-06-30-07:00</wd:End_Date>
<wd:Worker_Costing_Allocation_Detail_Data>
<wd:Order>a</wd:Order>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="FD30 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Fund_ID">FD30</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="CC0565 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">CC0565</wd:ID>
<wd:ID wd:type="Cost_Center_Reference_ID">CC0565</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PG00003 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Program_ID">PG00003</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="UD000004 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">UD000004</wd:ID>
<wd:ID wd:type="Custom_Organization_Reference_ID">UD000004</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PJ122201 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Project_ID">PJ122201</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Distribution_Percent>1</wd:Distribution_Percent>
</wd:Worker_Costing_Allocation_Detail_Data>
</wd:Worker_Costing_Allocation_Interval_Data>
<wd:Worker_Costing_Allocation_Interval_Data>
<wd:Costing_Override_ID>COSTING_OVERRIDE-X-YYYY</wd:Costing_Override_ID>
<wd:Start_Date>2017-01-01-08:00</wd:Start_Date>
<wd:End_Date>2017-06-30-07:00</wd:End_Date>
<wd:Worker_Costing_Allocation_Detail_Data>
<wd:Order>a</wd:Order>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="FD30 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Fund_ID">FD30</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="LOA">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">LOA</wd:ID>
<wd:ID wd:type="Custom_Organization_Reference_ID">LOA</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="CC0565 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">CC0565</wd:ID>
<wd:ID wd:type="Cost_Center_Reference_ID">CC0565</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PG00003 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Program_ID">PG00003</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="UD000004 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Organization_Reference_ID">UD000004</wd:ID>
<wd:ID wd:type="Custom_Organization_Reference_ID">UD000004</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Costing_Override_Worktag_Reference wd:Descriptor="PJ122201 XXXXX YYYYY">
<wd:ID wd:type="WID">WIDXXXXX</wd:ID>
<wd:ID wd:type="Project_ID">PJ122201</wd:ID>
</wd:Costing_Override_Worktag_Reference>
<wd:Distribution_Percent>1</wd:Distribution_Percent>
</wd:Worker_Costing_Allocation_Detail_Data>
</wd:Worker_Costing_Allocation_Interval_Data>
</wd:Worker_Costing_Allocations_Data>
</wd:Response_Data>
</wd:Get_Worker_Costing_Allocations_Response>
</env:Body>
</env:Envelope>
If you need to get data from multiple levels which each have multiple nodes, you can use multiple XMLTable constructs:
select x1.employee_id, x1.position_id,
to_date(x2.start_date, 'YYYY-MM-DD-HH24:MI') as start_date,
x2.order_1, x2.fund_id
from xml_table t
cross join xmltable (
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'urn:com.workday/bsvc' as "wd"),
'/env:Envelope/env:Body/wd:Get_Worker_Costing_Allocations_Response/wd:Response_Data/wd:Worker_Costing_Allocations_Data'
passing t.raw_xml
columns Employee_ID number path 'wd:Worker_Reference/wd:ID[@wd:type="Employee_ID"]',
Position_ID varchar2(100) path 'wd:Position_Reference/wd:ID[@wd:type="Position_ID"]',
Interval_Data xmltype path 'wd:Worker_Costing_Allocation_Interval_Data'
) x1
cross join xmltable (
xmlnamespaces ('urn:com.workday/bsvc' as "wd"),
'/wd:Worker_Costing_Allocation_Interval_Data/wd:Worker_Costing_Allocation_Detail_Data'
passing x1.Interval_Data
columns start_Date varchar2(16) path './../wd:Start_Date',
order_1 varchar2(20) path 'wd:Order',
fund_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Fund_ID"]'
) x2
/
EMPLOYEE_ID POSITION_ID START_DATE ORDER_1 FUND_ID
----------- ----------- ------------------- ------- -------
11111 P11111 2018-07-01 07:00:00 a FD89
11111 P11111 2018-07-01 07:00:00 b FD89
11111 P11111 2017-07-01 07:00:00 a FD30
11111 P11111 2017-01-01 08:00:00 a FD30
The first XMLTable gets the employee and position IDs, and its own XMLType which is the Worker_Costing_Allocation_Interval_Data fragment. The second XMLTable is passed that fragment, and extracts the multiple values at that level. You can go down more levels if you need to.
Notice that the filters for the specific instances of some nodes have changed; rather than using [2] to get the second worker reference ID for instance, I've looked for a specific attribute value with [@wd:type="Employee_ID"] etc. And a similar construct is sued for the fund ID; your original code was loking for an attribute called Fund_ID, not one with that type value.
You can do it in one level, but the references back up the tree can get a bit confusing. This gets all the fields you want:
select x.employee_id, x.position_id,
to_date(x.start_date, 'YYYY-MM-DD-HH24:MI') as start_date,
to_date(x.end_date, 'YYYY-MM-DD-HH24:MI') as end_date,
x.order_1, x.fund_id, x.project_id, x.program_id, x.Cost_Center_Reference_ID,
x.custom_organization_ref_id, x.distribution_percent
from xml_table t
cross join xmltable (
xmlnamespaces ('http://schemas.xmlsoap.org/soap/envelope/' AS "env",
'urn:com.workday/bsvc' as "wd"),
'/env:Envelope/env:Body/wd:Get_Worker_Costing_Allocations_Response/wd:Response_Data/wd:Worker_Costing_Allocations_Data/wd:Worker_Costing_Allocation_Interval_Data/wd:Worker_Costing_Allocation_Detail_Data'
passing t.raw_xml
columns employee_id number path './../../wd:Worker_Reference/wd:ID[@wd:type="Employee_ID"]',
position_id varchar2(100) path './../../wd:Position_Reference/wd:ID[@wd:type="Position_ID"]',
start_date varchar2(16) path './../wd:Start_Date',
end_date varchar2(16) path './../wd:End_Date',
order_1 varchar2(20) path 'wd:Order',
fund_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Fund_ID"]',
project_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Project_ID"]',
program_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Program_ID"]',
cost_center_reference_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Cost_Center_Reference_ID"]',
custom_organization_ref_id varchar2(20) path 'wd:Costing_Override_Worktag_Reference/wd:ID[@wd:type="Custom_Organization_Reference_ID" and starts-with(text(), "UD")]/text()',
distribution_percent number path 'wd:Distribution_Percent'
) x
/
EMPLOYEE_ID POSITION_ID START_DATE END_DATE ORDER_1 FUND_ID PROJECT_ID PROGRAM_ID COST_CENTE CUSTOM_ORG DISTRIBUTION_PERCENT
----------- ----------- ------------------- ------------------- ------- ------- ---------- ---------- ---------- ---------- --------------------
11111 P11111 2018-07-01 07:00:00 2050-06-30 07:00:00 a FD89 PJ122201 PG11130 CC12340 0.4
11111 P11111 2018-07-01 07:00:00 2050-06-30 07:00:00 b FD89 PJ122201 PG11130 CC1234 0.6
11111 P11111 2017-07-01 07:00:00 2018-06-30 07:00:00 a FD30 PJ122201 PG00003 CC0565 UD000004 1.0
11111 P11111 2017-01-01 08:00:00 2017-06-30 07:00:00 a FD30 PJ122201 PG00003 CC0565 UD000004 1.0
I've commented out the Custom_Organization_Reference_IDbecause there are duplicates - LOA and UD000004 - so you'd probably have to go back to multiple XMLTables to handle that anyway.
This only finds Custom_Organization_Reference_ID that start with UD, as per your comment.