DEVHIDE
Login Or Sign up

pull data using xmltable and XMLNAMESPACES

1.1k Views Asked by At

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>
sql xml oracle qxmlquery
Original Q&A
1

There are 1 best solutions below

5
On BEST ANSWER

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.

Related Questions in SQL

Related Questions in XML

Related Questions in ORACLE

Related Questions in QXMLQUERY

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Popular Questions

Copyright © 2021 Jogjafile Inc.