Loading

Using SQL XMLType in Mule 4

Udgivelsesdato: May 28, 2025
Opgave

GOAL

The purpose of this guide is how to use XMLType in Mule 4 for MS SQL and Oracle databases.
Trin
The Database connector can coerce a String into XMLType by it self. The example below is a DW 2 script that maps a JSON array into a text/plain output.

Script: 
%dw 2.0
output text/plain
---
"<?xml version=\"1.0\"' encoding=\"UTF-8\"?> "
++ write({
XMLDATA: {
(payload map ( payload01 , indexOfPayload01 ) -> {
Person: {
Name: payload01.Name,
Value: payload01.Value as Number,
Type: payload01.Type as Number
}
})
}
} , "application/xml",{writeDeclaration: false})
Input: 
[
{
"Name": "John Doe",
"Value": "11",
"Type": "1"
},
{
"Name": "Susan Doe",
"Value": "1234567",
"Type": "2"
}
]

Output 
<?xml version="1.0"' encoding="UTF-8"?> 
<XMLDATA>
  <Person>
    <Name>John Doe</Name>
    <Value>11</Value>
    <Type>1</Type>
  </Person>
  <Person>
    <Name>Susan Doe</Name>
    <Value>1234567</Value>
    <Type>2</Type>
  </Person>
</XMLDATA>

Insert Example: 
  
<db:insert doc:name="Insert" doc:id="eed154f0-ace1-47f3-bc70-5bc7eb1b1dde" config-ref="Database_Config_XE">
			<db:sql>INSERT INTO hr.warehouses (warehouse_id,warehouse_spec,warehouse_name) VALUES (17, :warehouse_spec,'BarnesNobles')</db:sql>
			<db:input-parameters><![CDATA[#[{'warehouse_spec' : payload}]]]></db:input-parameters>
</db:insert>


Store Procedure 
<db:stored-procedure doc:name="Store" doc:id="f6c9ade1-2e38-459c-ab0f-421e49656ef1" config-ref="Database_Config">
	<db:sql >{CALL SP_MySP(:xml)}</db:sql>
	<db:input-parameters ><![CDATA[#[{xml: payload}]]]></db:input-parameters>
</db:stored-procedure>

 
Vidensartikelnummer

001114563

 
Indlæser
Salesforce Help | Article