Satish Satish - 3 years ago 153
SQL Question

pull data using xmltable and XMLNAMESPACES

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>

Answer Source

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 almost 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"]',
      distribution_percent varchar2(20) path 'wd:Distribution_Percent'
) x
/

EMPLOYEE_ID POSITION_ID START_DATE          END_DATE            ORDER_1 FUND_ID PROJECT_ID           PROGRAM_ID           COST_CENTER_REFERENC 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               1                   
      11111 P11111      2017-01-01 08:00:00 2017-06-30 07:00:00 a       FD30    PJ122201             PG00003              CC0565               1                   

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download