Question 174130 in Azure Question posted in
The official documentation to get you started can be found here.

Azure – Unable to convert XML query in ADF

There is set variable holding the below value and 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 the 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,’

Tried to use replace function.

Tags:

2

Answers


  1. 0

    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: Sophia Owen

    Login or Signup to reply.
  2. 0

    I tried to replicate your issue in my environment by giving below values as set variable 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>

    I tried to pass set variable value with your expression as additional column value

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

    I got the below error:

    enter image description here

    In ADF '' used to mention the value as string so no need to remove back slash ('') The output just displayed with '' but it can take it as provided format only. you can directly pass the variable value to the additional column. For your clarification you can check with string length both will be equal.
    I created set variable strlen with @string(length(variables('xml'))) to find above xml lengh, I got below output:

    enter image description here

    I copied above xml data to the csv file by adding additional column with expression @variables('xml') to my required destination.

    enter image description here

    To find length above I added above file as source data and created derived column with length(xml) I got below output:

    enter image description here

    So, without removing '' you can directly pass the variable value.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search