Forum Discussion
Parse/Flatten the csv file in which one column is XML using Dataflow in Azure Data Factory
We have CSV file in this format. UID Catalog_Names 152376 <catalogs> <Catalog Name="NEWYORK" RequireApprove="True"/> </catalogs> 413683 <catalogs> <Catalog Name="BOSTON" Requi...
Using Parse Transformation in Data flow :
Source XML with Attribute data:
<cars>
<car model="camaro">
<year>1989</year>
</car></cars>
Expression: (cars as (car as ({@model} as string, year as integer)))
Note: If you run into errors extracting attributes (i.e. @model) from a complex type, a workaround is to convert the complex type to a string, remove the @ symbol (i.e. replace(toString(your_xml_string_parsed_column_name.cars.car),'@','') ), and then use the parse JSON transformation activity.
Source XML with Attribute data:
<cars>
<car model="camaro">
<year>1989</year>
</car></cars>
Expression: (cars as (car as ({@model} as string, year as integer)))
Note: If you run into errors extracting attributes (i.e. @model) from a complex type, a workaround is to convert the complex type to a string, remove the @ symbol (i.e. replace(toString(your_xml_string_parsed_column_name.cars.car),'@','') ), and then use the parse JSON transformation activity.
<catalogs>
<Catalog Name="NEWYORK"
RequireApprove="True"/>
</catalogs>
Exp:
(catalogs as {replace(toString(u_catalog.catalogs),'@','')Catalog Name} as string,
{replace(toString(u_catalog.catalogs),'@','')RequireApprove} as string)
not working