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.

score 5 · Accepted Answer · answered May 25 '17 at 11:22

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.

Had similar issue only trying to provide array as root. When adding 'ResultSets' for example as root, then parsing works fine. — Taurib, Sep 30 '19 at 08:35

score 0 · Answer 2 · answered Aug 21 '18 at 09:50

0

In order to convert to XML, the JSON needs to have a single root element.

The first example has multiple elements at the root level, which is what the error message is complaining about in "This document already has a 'DocumentElement' node".

Your 'correct' JSON does have a single root element.

answered Aug 21 '18 at 09:50

Andrew Bennett

13
1
4

This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/low-quality-posts/20644640) – Gerhard Aug 21 '18 at 10:53
@GerhardBarnard look again, this is an answer. It states OP's JSON is malformed and can't be converted because it can only have a single root element. – Zoe Aug 21 '18 at 12:24

Convert SQL Stored procedure ResultSet table JSON to XML

2 Answers2

Linked