ADF How to save a csv from a variable to a blob storage?

Question

I am making two calls(Web HTTP activity) to a SOAP .asmx service (2 factor auth) and I'm receiving the following JSON Response:

{ 	"Response":"<?xml version=\"1.0\" encoding=\"utf-8\"?> 		
		<soap:Envelope xmlns:soap=\"http://www.w3.org/2003/05/soap-envelope\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\"> 			<soap:Body><GetHistoryDataResponse xmlns=\"http://tempuri.org/\">
<GetHistoryDataResult> 						
			here is the csv file with column headers 					
</GetHistoryDataResult>
</GetHistoryDataResponse>
</soap:Body>
</soap:Envelope> 	
" 
}

I need to save the content of the <GetHistoryDataResult> node into a csv file(with headers on the first row).

I tried to parse the xml and save it into a variable:

Set variable 1(XmlString) with @activity('GetHistoryData').output.Response which will extract the xml part

Set variable 2(CsvData) with something like this:

@substring(variables('XmlString'),add(indexOf(variables('XmlString'), 'GetHistoryDataResult'),21),sub(lastIndexOf(variables('XmlString'), 'GetHistoryDataResult'), add(indexOf(variables('XmlString'), 'GetHistoryDataResult'),23)))

which will extract the csv part

In the final variable I have the whole csv like this:

		{
			"name": "CsvData",
			"value": "csvCol1;csvCol2;.....
						val1;val2;val3....
					"
		}

I've added the copy activity with a dummy source, an additional column named VariableColumn with the value @variables('CsvData') and a sink with a dataset delimited text blob.

The problem is that no matter what I set in the mapping, it always copies a destination column and the double quotes ("") at the beginning and end of the csv file like this:

and because of this I'm not being able to further map it(or transform it) and use the columns because it does not see it as a valid csv.

Answer 1

Hi you can go to copy activity sink and remove quote character, set it to no quote character and try again.

Answer 2

From what you provided so far, it that seems you're trying to extract CSV data embedded in a SOAP response and then store it into Azure Blob Storage. It can be tricky when you're dealing with complex XML structures.

From what you've shared, it looks like the issue is related to how the CSV data is extracted and subsequently saved. The double quotes and incorrect formatting may stem from how the variables and expressions are being used.

Try to use an Azure Function, Logic App, or Data Flow transformation to parse the XML and extract the CSV content. You could build a custom logic using C#, Python, or another language, and host it in an Azure Function. The code will look for the <GetHistoryDataResult> element in the XML and extract its value.

```csharp

using System.Xml.Linq;

public static string ExtractCsvFromXml(string xmlContent)

{

    XDocument doc = XDocument.Parse(xmlContent);

    XNamespace ns = "http://tempuri.org/";

    string csvData = doc.Descendants(ns + "GetHistoryDataResult").First().Value;

    return csvData;

}

Then you can use an Azure Function activity to call the Azure Function, passing the extracted XML as a parameter. The result of this activity should be the CSV data.

Finally you can use the Copy Data activity to copy the extracted CSV data to Azure Blob Storage. For the Source, use an Inline dataset, and set the content to the output of the Azure Function activity. Use an Azure Blob dataset with the appropriate linked service for the Destination.