CODEWITHSUNDEEP
sql-serverxmlmuledataweavemulesoft
Steve
Steve

Reputation: 475

How do you convert SQL Server SELECT into XML in Mule 4?

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

The output in SQL is:

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

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">[email protected]</ns0:Email>
    <ns0:Email Type="sec">[email protected]</ns0:Email>
  </ns0:SchoolEmailList>
</ns0:SchoolInfo>

Thanks, Steve

Upvotes: 1

Views: 567

Answers (2)

user3078986
user3078986

Reputation:

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

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

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

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

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 :).

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.

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

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: ([email protected], acc={}) -> acc + {Email: [email protected]}
 * 2nd iteration: ([email protected], acc={Email: [email protected]} -> acc + {Email: [email protected]}
 * result: acc = {Email: [email protected], Email: [email protected]}
 */

Upvotes: 5

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22187

Just a follow up on my comment above. The entire solution via T-SQL.

SQL

-- DDL and sample data population, start
DECLARE @SchoolInfo TABLE 
(
    RefId VARCHAR(40) PRIMARY KEY,
    LocalId INT,
    StateProvinceId CHAR(2),
    SchoolName VARCHAR(30)
);
DECLARE @SchoolEmail TABLE 
(
    ID INT PRIMARY KEY, 
    RefId VARCHAR(40), 
    Email VARCHAR(30), 
    EmailType CHAR(3)
);
INSERT @SchoolInfo (RefId, LocalId, StateProvinceId, SchoolName) VALUES
('7FDF722B-6BBA-4BF0-8205-A5380B269EF1', 1, 'CA', 'Steve''s School');
INSERT INTO @SchoolEmail (ID, RefId, Email, EmailType) VALUES
(1, '7FDF722B-6BBA-4BF0-8205-A5380B269EF1', '[email protected]', 'prm')
,(2, '7FDF722B-6BBA-4BF0-8205-A5380B269EF1', '[email protected] ', 'sec');
-- DDL and sample data population, end

DECLARE @ref_id VARCHAR(40) = '7FDF722B-6BBA-4BF0-8205-A5380B269EF1';

;WITH xmlnamespaces ('http://www.sifassociation.org/datamodel/au/3.4' AS ns0)
SELECT s.RefId AS [@RefId]
    , s.LocalId AS [ns0:LocalId]
    , s.StateProvinceId AS [ns0:StateProvinceId]
    , s.SchoolName AS [ns0:SchoolName]
, (
    SELECT e.EmailType AS [ns0:Email/@Type]
        , e.Email AS [ns0:Email]
    FROM @SchoolEmail AS e
    WHERE e.RefId = s.RefId
    FOR XML PATH(''), TYPE, ROOT('ns0:SchoolEmailList')
)
FROM @SchoolInfo AS s
WHERE s.RefId = @ref_id
FOR XML PATH('ns0:SchoolInfo'), TYPE;

Output

<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>CA</ns0:StateProvinceId>
  <ns0:SchoolName>Steve's School</ns0:SchoolName>
  <ns0:SchoolEmailList xmlns:ns0="http://www.sifassociation.org/datamodel/au/3.4">
    <ns0:Email Type="prm">[email protected]</ns0:Email>
    <ns0:Email Type="sec">[email protected] </ns0:Email>
  </ns0:SchoolEmailList>
</ns0:SchoolInfo>

Upvotes: 1

Related Questions