CODEWITHSUNDEEP
databaseetlxquerynetezzaxquery-sql
ImSahil
ImSahil

Reputation: 81

Loading the data into Netezza database using xquery from XML source

I have a source as XML and has a huge number of records. just for the sample I have pasted 1 record below :

<?xml version='1.0' encoding='UTF-8'?><wd:Report_Data xmlns:wd="urn:com.workday.report/BCF-Termination-Details">
<wd:Report_Entry>
            <wd:Worker>
                    <wd:Associate_ID>997215</wd:Associate_ID>
                    <wd:Total_Base_Pay_Amount>13</wd:Total_Base_Pay_Amount>
                    <wd:Total_Base_Pay_Currency wd:Descriptor="USD"><wd:ID wd:type="WID">9e996ffdd3e14da0ba7275d5400bafd4</wd:ID><wd:ID wd:type="Currency_ID">USD</wd:ID><wd:ID wd:type="Currency_Numeric_Code">840</wd:ID></wd:Total_Base_Pay_Currency>
                    <wd:Length_of_Service_-_Position>0 year(s), 4 month(s), 7 day(s)</wd:Length_of_Service_-_Position>
            </wd:Worker>
            <wd:Time_Type wd:Descriptor="Part time"><wd:ID wd:type="WID">3baf0a7f595210daec53e26fa7476d5b</wd:ID><wd:ID wd:type="Position_Time_Type_ID">Part_time</wd:ID></wd:Time_Type>
            <wd:Hire_Date>2022-05-25-07:00</wd:Hire_Date>
            <wd:Termination_Date>2022-10-02-07:00</wd:Termination_Date>
            <wd:Date_Initiated>2022-10-28T17:39:53.943-07:00</wd:Date_Initiated>
            <wd:Termination_Category>Voluntary</wd:Termination_Category>
            <wd:Termination_Reason>Job Abandonment</wd:Termination_Reason>
            <wd:Length_of_Service_in_Days>130</wd:Length_of_Service_in_Days>
            <wd:workdayID>f415ada264f1100211408522a0e00000</wd:workdayID>
</wd:Report_Entry></wd:Report_Data>

I need this to implement in ETL. Using xml as source need few of the columns from the xml and load into the database. I am new to xquery, so need to know how can we start it. I am doing the POC on this.

Upvotes: 0

Views: 161

Answers (1)

Nikita Nirbhavane
Nikita Nirbhavane

Reputation: 41

If you want to extract the values from xml source you can try using the XML functions from SQLEXT Toolkit package which can be installed on top of Netezza.

Here is an example of fetching the associate_id from the xml source. You can enter the extracted value in a table.

select xmlextractvalue(xmlparse(ele_string),'/Report_Entry/Worker/Associate_ID') as associate_id from (select replace(element,'wd:','') as ele_string from t1) as foo;

ASSOCIATE_ID
--------------
 997215
(1 row)

Upvotes: 2

Related Questions