I want to convert xml to custom string format. If any one know please provide some inputs
Expect format:
status# statusMsg# version# key# keyType# acctID# dateStmtBegin~datePost~timePost~acctNbr~acctNbrOrig~refNbr~tranCode~amtTran~debitCredit~dateTran~transDesc~DBAName]DBACntry~stmtPrint
Input xml is :
<status>000</status>
<statusMsg>Passed</statusMsg>
<version>1.2</version>
<key>1234********4567</key>
<keyType>cardNbr</keyType>
<acctID>3052323265</acctID>
<tranDetail>
<dateStmtBegin>23-02-2017</dateStmtBegin>
<datePost>2017-12-12</datePost>
<timePost>3453456</timePost>
<acctNbr>1234********5555</acctNbr>
<acctNbrOrig>1234********5556</acctNbrOrig>
<refNbr>67899</refNbr>
<tranCode>405</tranCode>
<amtTran>708</amtTran>
<debitCredit>505</debitCredit>
<dateTran>23-07-2017</dateTran>
<transDesc>Approved</transDesc>
<merchantInfo>
<DBAName>DBA</DBAName>
<DBACntry>IND</DBACntry>
</merchantInfo>
<stmtPrint>23-07-2017</stmtPrint>
</tranDetail>
<tranDetail>
<dateStmtBegin>23-02-2017</dateStmtBegin>
<datePost>2017-02-07</datePost>
<timePost>1010</timePost>
<acctNbr>1234********5555</acctNbr>
<acctNbrOrig>1234********5556</acctNbrOrig>
<refNbr>100</refNbr>
<tranCode>405</tranCode>
<amtTran>708</amtTran>
<debitCredit>505</debitCredit>
<dateTran>23-07-2017</dateTran>
<transDesc>Approved</transDesc>
<merchantInfo>
<DBAName>DBA</DBAName>
<DBACntry>IND</DBACntry>
</merchantInfo>
<stmtPrint>23-07-2017</stmtPrint>
</tranDetail>
Note:1.keys should append with # .
2. subkeys append with ~
@eswar49 , I'm providing a working solution but that includes a couple of assumptions on the payload and the output expected since this is supposed to work for the more generic cases.
Use this DW:
%dw 1.0
%output application/json
%var keysep = ["# ","~","]"]
%var pairsep = "|"
%var arraysep = "^"
%function serializeObject(level,keypath,element)
serializeKey(level,keypath,element) ++ pairsep ++ serializeValue(level,keypath,element)
%function serializeKey(level,keypath,element)
element match {
e is :object -> e pluck serializeKey(level+1,$$,$) joinBy keysep[level],
e is :array -> e map serializeKey(level,keypath,$) joinBy arraysep,
e is :null -> {},
default -> (keypath)
}
%function serializeValue(level,keypath,element)
element match {
e is :object -> e pluck serializeValue(level+1,$$,$) joinBy keysep[level],
e is :array -> e map serializeValue(level,keypath,$) joinBy arraysep,
e is :null -> {},
default -> (element)
}
---
serializeObject(0,"",payload)
This is a how it works:
- keys and values are serialized separated as KEYS | VALUES.
- keys (and value separators) are as seen in the output based on level of the object: "# " for first level, "~" for second level, and "]" for third level
- elements of the collection are separated by "^"
- The main payload is recursively traversed and serialized.
Now, your XML doesn't have a root element and the `</tranDetail>` tag should be wrapped by a `</tranDetails>` since you expect a collection. The DW works assuming this is fixed. Anyway, you can test it with JSON equivalent:
{
"status": "000",
"statusMsg": "Passed",
"version": "1.2",
"key": "1234********4567",
"keyType": "cardNbr",
"acctID": "3052323265",
"tranDetails": [{
"dateStmtBegin": "23-02-2017",
"datePost": "2017-12-12",
"timePost": "3453456",
"acctNbr": "1234********5555",
"acctNbrOrig": "1234********5556",
"refNbr": "67899",
"tranCode": "405",
"amtTran": "708",
"debitCredit": "505",
"dateTran": "23-07-2017",
"transDesc": "Approved",
"merchantInfo": {
"DBAName": "DBA",
"DBACntry": "IND"
},
"stmtPrint": "23-07-2017"
},
{
"dateStmtBegin": "23-02-2017",
"datePost": "2017-02-07",
"timePost": "1010",
"acctNbr": "1234********5555",
"acctNbrOrig": "1234********5556",
"refNbr": "100",
"tranCode": "405",
"amtTran": "708",
"debitCredit": "505",
"dateTran": "23-07-2017",
"transDesc": "Approved",
"merchantInfo": {
"DBAName": "DBA",
"DBACntry": "IND"
},
"stmtPrint": "23-07-2017"
}]
}
You should see an output like this:
status# statusMsg# version# key# keyType# acctID# dateStmtBegin~datePost~timePost~acctNbr~acctNbrOrig~refNbr~tranCode~amtTran~debitCredit~dateTran~transDesc~DBAName]DBACntry~stmtPrint^dateStmtBegin~datePost~timePost~acctNbr~acctNbrOrig~refNbr~tranCode~amtTran~debitCredit~dateTran~transDesc~DBAName]DBACntry~stmtPrint|000# Passed# 1.2# 1234********4567# cardNbr# 3052323265# 23-02-2017~2017-12-12~3453456~1234********5555~1234********5556~67899~405~708~505~23-07-2017~Approved~DBA]IND~23-07-2017^23-02-2017~2017-02-07~1010~1234********5555~1234********5556~100~405~708~505~23-07-2017~Approved~DBA]IND~23-07-2017
