error while converting xml query in ADF

Josh 46 Reputation points
2023-05-22T09:25:50.2333333+00:00

There is set variable holding the below value , it is of type string .

{
    "variableName": "test",
    "value": "<?xml version=\"1.0\" encoding=\"utf-8\"?> <fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" aggregate=\"true\" distinct=\"false\"> <entity name=\"account\"> <attribute name=\"ownerid\" alias=\"account\" aggregate=\"count\" /> <filter type=\"and\"> <condition attribute=\"ownerid\" operator=\"not-null\" /> </filter> <link-entity name=\"team\" from=\"teamid\" to=\"owningteam\" link-type=\"inner\" alias=\"ae\"> <filter type=\"and\"> <condition attribute=\"teamtype\" operator=\"eq\" value=\"0\" /> <condition attribute=\"name\" operator=\"like\" value=\"%xyz%\" /> </filter> </link-entity> </entity> </fetch>"
}

Now while passing the below xml i'm using the below replace function to remove \ .

@xml(replace(variables('test'),'', '' )).

but it is giving th below error :

ErrorCode=UserErrorInvalidValueInPayload,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to convert the value in 'query' property to 'System.String' type. Please make sure the payload structure and value are correct.,Source=Microsoft.DataTransfer.DataContracts,''Type=System.InvalidCastException,Message=Object must implement IConvertible.,Source=mscorlib,'


Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,111 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,326 Reputation points Microsoft Employee
    2023-05-23T09:45:50.9866667+00:00

    Hi Josh,

    Thank you for posting query in Microsoft Q&A Platform.

    Please note, in your value \ are escape characters to escape double quotes("). When you see any data in output json of activity if there is any double quote then it use \ as escape character to represent it. But in reality that \ is not part of your value.

    Regarding your expressions `@xml(replace(variables('test'),'', '' )).

    Here you are trying to replace empty with empty, that means nothing will change and also you are using returned value as input to xml()function. XML function input should not XML string. It should different kind of string which can then convert to XML.

    For example, in below example I am using json string as input to XML() function. Hence its finally able to generate XML from it.

    xml(json('{ "name": "Sophia Owen" }'))

    output: <name>Sophia Owen</name>

    Hope this helps. Please let me know if any further queries.


    Please consider hitting Accept Answer button. Accepted answers help community as well.