将SQL存储过程ResultSet表JSON转换为XML [英] Convert SQL Stored procedure ResultSet table JSON to XML

问题描述

这看起来很明显，但是不知怎么对我不起作用.我正在尝试在Microsoft Azure上的Logic App中构建解决方案，但无法将JSON对象转换为XML.

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.

我的要求是执行存储过程，并将响应保存为XML格式.默认情况下，SQL Execute存储过程操作以以下JSON格式返回响应，

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
      }]
 }
}

然后在创建Blob"操作中使用以上响应，以将响应保存在Azure上的Blob中.

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

此链接表示逻辑应用程序提供了xml函数，可将字符串或JSON对象转换为XML，但这似乎无法正常工作.我尝试使用下面的表达式，但是没有用，

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,

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

错误:模板语言功能'xml'参数无效.提供的值不能转换为XML:此文档已具有'DocumentElement'节点.".有关用法的详细信息，请参见 https://aka.ms/logicexpressions#xml .

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.

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

错误:模板语言函数"xml"期望其参数为字符串或对象.提供的值是数组"类型.有关用法的详细信息，请参见 https://aka.ms/logicexpressions#xml .

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.

我只想将此JSON转换为如下所示的XML，

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

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

另一种解决方案可能是调用Azure函数，然后用c#代码将此JSON转换为XML.但是在尝试其他解决方案之前，我想知道我在做错什么.

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.

推荐答案

发布问题后，我进一步分析了该问题，发现我在@xml函数中传递了错误的JSON对象.

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

正确的JSON对象应如下所示，

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,'}')))

现在，我需要稍微调整一下此表达式以获取最终的XML.希望这对某人有帮助.

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

这篇关于将SQL存储过程ResultSet表JSON转换为XML的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！