Flatten / Parsing XML using Azure ADF (Azure data factory)

 

                         Flatting/Parsing Hierarchical XML using ADF (Azure data factory)


Sharing a method of flatting Hierarchical XML using an inbuild method in ADF  ( since its UI based I felt its easier than databricks option)

 To flatten “Hierarchical XML” you can use “Flatten” transform in ADF Dataflow.

 My source XML structure is mentioned below.



To Flatten the structure , we can create a ADF dataflow similar to screen shot mentioned below and use ‘Flatten’ transformation.


Steps involved :

Step 1: Create a dataflow in ADF

Step 2: Enable “Data flow debug” option.

·       This will create a cluster in the backend, for the dataflow to run.

·       Cost of this option is priced as 1(hour) x 8 (general purpose cores) x $0.274 = $2.19

·       This is required only during the development phase


Step 3: Select the Source, Import Schema in Projection tab


Data Preview:


Expanding the PARTY sub-set


Import Schema:


Step 4: Click on the plus symbol (+) and choose “Flatten” option



·       Select the Unroll By option (understanding: lowest array in the XML) and add the required field mapping

 





·   Refresh the data preview


 


Step 5: Add the sink (target)


Select the Data set as required.




Step 6: Create a pipeline to execute the data flow




·       DB screen shot:






Comments

Post a Comment

Popular posts from this blog

SCD 2 Using Merge statement in SQL server

Image
 In this example we will look at how to implement SCD2 using merge statement. Target table: Source Table: So if you compare the source and target table , you can see that  * New address for customerid = 1 in the source table , so "New" address needs to be inserted (true) and  "current" address in traget table (customer) needs to be updated as 'false'. * No changes to the address of customer 3 , so no changes. * A "new" customer with customerid = 4 , so that needs to be inserted. Query for SCD 2 : MERGE INTO customers USING (    -- These rows will either UPDATE the current1 addresses of existing customers or INSERT the new addresses of new ----customers   SELECT updates.customerId as mergeKey, updates.*   FROM updates      UNION ALL      -- These rows will INSERT new addresses of existing customers    -- Setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTed.   SELECT NULL as mergeKe...
Read more

Integer division to float division in SQL server

Image
Problem statement : I have a use case where i need to see how much percentage each day with respect to days in a week. Solution in Mathematics :  since there are 7 days in a week , so the solution will be (1/7) * 100 = 14.28 Solution in SQL server: If we apply the same logic in SQL server the result will be 0 because with will be a integer vision. To convert this into float division we need to us 'cast (<integer> as float)' in the denominator. Please refer to the screen shot below.  
Read more