如何在Mule 4中将SQL Server SELECT转换为XML? [英] How do you convert SQL Server SELECT into XML in Mule 4?

问题描述

如何使用Mule 4中的Dataweave将以下SQL输出转换为XML?

How can I convert the following SQL output into XML using Dataweave in Mule 4?

SELECT s.RefId
          ,s.LocalId
          ,s.StateProvinceId
          ,s.SchoolName
          ,e.Email
          ,e.EmailType
      FROM SchoolInfo s
      LEFT OUTER JOIN SchoolEmail e
      ON    e.SchoolRefId = s.RefId
      WHERE s.RefId = :ref_id

SQL的输出是:

RefId                               LocalId StateProvinceId SchoolName      Email               Type
7FDF722B-6BBA-4BF0-8205-A5380B269EF1    1   SA              Steve's School  [e-mail]    prm
7FDF722B-6BBA-4BF0-8205-A5380B269EF1    1   SA              Steve's School  [e-mail]      sec

XML输出应如下所示:

The XML output should look like this:

<ns0:SchoolInfo xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4" RefId="7FDF722B-6BBA-4BF0-8205-A5380B269EF1">
  <ns0:LocalId>1</ns0:LocalId>
  <ns0:StateProvinceId>SA</ns0:StateProvinceId>
  <ns0:SchoolName>Steve's School</ns0:SchoolName>
  <ns0:SchoolEmailList>
    <ns0:Email Type="prm">[e-mail]</ns0:Email>
    <ns0:Email Type="sec">[e-mail]</ns0:Email>
  </ns0:SchoolEmailList>
</ns0:SchoolInfo>

谢谢，史蒂夫

推荐答案

以下是DW表达式，它将生成相同的XML:

Here's the DW expression that will generate the same XML:

%dw 2.0
output application/xml
ns ns0 http://www.sifassociation.org/datamodel/au/3.4
var rId = payload[0].RefId
var lId = payload[0].LocalId
var sId = payload[0].StateProvinceId
---
ns0#SchoolInfo @(RefId: rId): {
    ns0#LocalId: lId,
    ns0#StateProvinceId: sId,
    ns0#SchoolEmailList: payload reduce (e,acc={}) -> acc ++ {
        ns0#Email @(Type: e.Type): e.Email
    }   
} 

我假设每个查询的 RefId ， LocalId 和 StateProvinceId 始终相同.

I assume the RefId, LocalId, and StateProvinceId will always be same per query.

reduce 的解释:在此处详细说明了 reduce 其理论基础.这也是 reduce 的MuleSoft文档页面.这最后一页很好地解释了 reduce

Explanation of reduce: reduce is explained here in detail along with its theoretical foundations. Here's also reduce's MuleSoft documentation page. This last page does a pretty good job explaining reduce

现在用我自己的话说， reduce 将(1)一个数组和(2)一个lambda函数作为输入.

Now in my own words, reduce takes as an input (1) an array and (2) a lambda function.

该数组包含元素 reduce 将以与 map 函数相似的方式进行迭代. map 和 reduce 函数之间的相似之处到此为止:).

The array contains the elements reduce will iterate over in a similar fashion as a map function does. The similarities between the map and reduce functions ends here :).

lambda函数需要两个参数:(1)您要从数组中迭代的当前元素，以及(2)累加器.可以将累加器初始化为一个值(在您的用例中，我将其设置为对象 {} ，因为XML不喜欢数组).将此FIRST迭代的lambda函数的结果设置为下一次迭代的累加器，依此类推.

The lambda function expects two arguments: (1) the current element you iterate from the array and (2) the accumulator. The accumulator can be initialized into a value (I set it to an object {} in your use-case because XML does not like arrays). The result of the lambda function for this FIRST iteration is set as the accumulator for the next iteration and so on.

reduce 的结果是对数组进行迭代后的累加器.

The result of reduce is the accumulator once iterating over the array is done.

因此，如果我要跟踪这个特定的 reduce ，它将看起来像这样，并且我简化了这些值的表示方式:

Thus, if I was to trace this specific reduce it will look something like this and I simplify the denotation of these values:

/*
 * 1st iteration: (e=[e-mail], acc={}) -> acc + {Email: [e-mail]}
 * 2nd iteration: (e=[e-mail], acc={Email: [e-mail]} -> acc + {Email: [e-mail]}
 * result: acc = {Email: [e-mail], Email: [e-mail]}
 */

这篇关于如何在Mule 4中将SQL Server SELECT转换为XML?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！