Convert SQL Stored procedure ResultSet table JSON to XML

Question

This looks pretty obvious but somehow its not working for me. I am trying to build a solution in Logic App on Microsoft Azure but I am stuck to convert JSON object to XML.

My requirement is to execute a Stored Procedure and save the response in XML format. By default SQL Execute Stored Procedure Action returns the response in below JSON format,

    {
"OutputParameters": { },
"ReturnCode": 0,
"ResultSets": {
"Table1": [
      {
        "ProductID": 680,
        "Name": "HL Road Frame - Black, 58",
        "ProductNumber": "FR-R92B-58",
        "Color": "Black",
        "StandardCost": 1059.31,
        "ListPrice": 1431.5,
        "Size": "58",
        "Weight": 1016.04
      },
      {
        "ProductID": 706,
        "Name": "HL Road Frame - Red, 58",
        "ProductNumber": "FR-R92R-58",
        "Color": "Red",
        "StandardCost": 1059.31,
        "ListPrice": 1431.5,
        "Size": "58",
        "Weight": 1016.04
      }]
 }
}

Above response is then used in "Create Blob" action to save response in blob on Azure.

This link says that logic app provides xml function to convert string or JSON object to XML but this seems to be not working as expected. I tried below expression but nothing works,

@xml(body('Execute_stored_procedure')?['ResultSets'])

ERROR: The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'This document already has a 'DocumentElement' node.'. Please see https://aka.ms/logicexpressions#xml for usage details.

@xml(body('Execute_stored_procedure')?['ResultSets']['Table1'])

ERROR: The template language function 'xml' expects its parameter to be a string or an object. The provided value is of type 'Array'. Please see https://aka.ms/logicexpressions#xml for usage details.

All I want is to convert this JSON to an XML like below,

<Root><Product>....</Product><Product>....</Product></Root>

The alternate solution could be calling a Azure Function and convert this JSON to XML in c# code. But before I try alternate solution I want to know what I am doing wrong.

pgcan pgcan · Accepted Answer · 2017-05-25T11:22:40

After posting question I further analysed the issue and found that I was passing the wrong JSON object in @xml function.

The correct JSON object should be as below,

{
"ResultSets": {
"Table1": [
      {
        "ProductID": 680,
        "Name": "HL Road Frame - Black, 58",
        "ProductNumber": "FR-R92B-58",
        "Color": "Black",
        "StandardCost": 1059.31,
        "ListPrice": 1431.5,
        "Size": "58",
        "Weight": 1016.04
      },
      {
        "ProductID": 706,
        "Name": "HL Road Frame - Red, 58",
        "ProductNumber": "FR-R92R-58",
        "Color": "Red",
        "StandardCost": 1059.31,
        "ListPrice": 1431.5,
        "Size": "58",
        "Weight": 1016.04
      }]
 }
}

Please note that I had to remove below to lines,

"OutputParameters": { },
"ReturnCode": 0,

So tried with below expression and it worked,

@xml(json(concat('{\"ResultSets\":',body('Execute_stored_procedure').ResultSets,'}')))

Now I need to little tweak this expression to get the final XML. Hope this helps someone.

Convert SQL Stored procedure ResultSet table JSON to XML

2 Answers