azureazure-data-factoryazure-storage-queues

Azure Data Factory ForEach Parsing QueueMessageList


I am using the dynamic expression below in ForEach Items to feed QueueMessageText from a QueueMessageList into subsequent pipeline process

@json(xml(activity('get_queue_message').output.Response)).QueueMessagesList.QueueMessage

This works well when there are more than 1 QueueMessageText within the QueueMessageList but fails when there is only 1 QueueMessageText with the following error message:

The function 'length' expects its parameter to be an array or a string. The provided value is of type 'Object'

These is the xml for single QuoteMessageText:

"
<?xml version=\"1.0\" encoding=\"utf-8\"?>
<QueueMessagesList>
    <QueueMessage>
        <MessageId>test1</MessageId>
        <InsertionTime>Thu, 15 Jun 2023 04:21:06 GMT</InsertionTime>
        <ExpirationTime>Thu, 22 Jun 2023 04:21:06 GMT</ExpirationTime>
        <PopReceipt>popreceipt1</PopReceipt>
        <TimeNextVisible>Thu, 15 Jun 2023 04:22:41 GMT</TimeNextVisible>
        <DequeueCount>2</DequeueCount>
        <MessageText>{\"sample\":\"test.txt\"}</MessageText>
    </QueueMessage>
</QueueMessagesList>"

And this is the xml for multiple QuoteMessageText within 1 list:

"
<?xml version=\"1.0\" encoding=\"utf-8\"?>
<QueueMessagesList>
    <QueueMessage>
        <MessageId>test1</MessageId>
        <InsertionTime>Thu, 15 Jun 2023 04:21:06 GMT</InsertionTime>
        <ExpirationTime>Thu, 22 Jun 2023 04:21:06 GMT</ExpirationTime>
        <PopReceipt>pop1</PopReceipt>
        <TimeNextVisible>Thu, 15 Jun 2023 06:14:57 GMT</TimeNextVisible>
        <DequeueCount>5</DequeueCount>
        <MessageText>{\"sample\":\"test.txt\"}</MessageText>
    </QueueMessage>
    <QueueMessage>
        <MessageId>ctest2</MessageId>
        <InsertionTime>Thu, 15 Jun 2023 06:13:39 GMT</InsertionTime>
        <ExpirationTime>Thu, 22 Jun 2023 06:13:39 GMT</ExpirationTime>
        <pop2</PopReceipt>
        <TimeNextVisible>Thu, 15 Jun 2023 06:14:57 GMT</TimeNextVisible>
        <DequeueCount>1</DequeueCount>
        <MessageText>{\"sample\":\"test2.txt\"}</MessageText>
    </QueueMessage>
</QueueMessagesList>"

I understand that when i convert the XML to JSON, XML with Multiple QueueMessageText will create a QueueMessage list while XML with single QueueMessageText will create a QueueMessageText dictionary. Please help me with a suggestion on how to modify the expression for the ForEach Activity as i cannot change the QueueMessageList output. Thank you


Solution

  • I have modified my expression as follows:

    @array(json(xml(activity('get_queue_message').output.Response)).QueueMessagesList.QueueMessage)
    

    It seems to work now.