How do I pass a null value to my SQL Server stored procedure using WSO2?

Question

In my sequence, I'm transforming a JSON request into XML so I can make a dataServiceCall to a SQL Server stored procedure that inserts into a table. This all works fine, but if one of the fields is null, it is inserted as an empty string ("") instead of NULL.

When I view the XML being sent to the dataServiceCall, it looks like this:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Body>
<InsertWebAppPath xmlns="http://ws.apache.org/ns/synapse">
<pathTemplate>/postmanTest</pathTemplate>
<label2>Postman Test</label2>
<icon>test</icon>
<isActive>false</isActive>
<label1>Postman Test</label1>
<label3 nil="true"/>
</InsertWebAppPath>
</soapenv:Body>
</soapenv:Envelope>

The transform from JSON to XML added the nil="true" attribute on label3, which I was expecting. But the insert still ends up as an empty string instead of NULL. I've tried adding a defaultValue of #{NULL} in my query, which makes no difference.

Here's my sequence:

  <property name="messageType" scope="axis2" type="STRING" value="text/xml"/>
  <payloadFactory media-type="xml">
    <format>
      <InsertWebAppPath>$1</InsertWebAppPath>
    </format>
    <args>
      <arg evaluator="json" expression="$"/>
    </args>
  </payloadFactory>
            
  <dataServiceCall description="InsertWebAppPath" serviceName="myService">
    <source type="body"/>
    <target type="body"/>
  </dataServiceCall>

And the dbs file:

    <query id="InsertWebAppPath" useConfig="myDSS">
        <sql>{ call InsertWebAppPath(?,?,?,?,?,?) }</sql>
        <param type="IN" name="pathTemplate" paramType="SCALAR" sqlType="STRING" optional="false" />
        <param type="IN" name="isActive" paramType="SCALAR" sqlType="BOOLEAN" optional="false" />
        <param type="IN" defaultValue="#{NULL}" name="label1" paramType="SCALAR" sqlType="STRING" optional="true" />
        <param type="IN" defaultValue="#{NULL}" name="label2" paramType="SCALAR" sqlType="STRING" optional="true" />
        <param type="IN" defaultValue="#{NULL}" name="label3" paramType="SCALAR" sqlType="STRING" optional="true" />
        <param type="IN" defaultValue="#{NULL}" name="icon" paramType="SCALAR" sqlType="STRING" optional="true" />
    </query>
    <operation name="InsertWebAppPath">
        <call-query href="InsertWebAppPath">
            <with-param name="pathTemplate" query-param="pathTemplate" />
            <with-param name="isActive" query-param="isActive" />
            <with-param name="label1" query-param="label1" />
            <with-param name="label2" query-param="label2" />
            <with-param name="label3" query-param="label3" />
            <with-param name="icon" query-param="icon" />
        </call-query>
    </operation>

The stored procedure is doing a straight insert of values, no manipulation. I've also confirmed it works if I call it with NULL outside of WSO2.

Answer 1

I was able to get this working after looking into AlwaysLearning's suggestion. I used an XSLT transform to add the xsi namespace and binding. I'm still open to other suggestions that don't use custom transforms but this is sufficient for my purposes.

EDIT: To be specific, I added this line to my sequence between the payloadFactory and dataServiceCall:

<xslt description="addXsiNamespace" key="conf:endpoints/addXsiNamespace.xslt"/>

And this is my XSLT mapping file in the resource registry:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0">
    <xsl:template match="node()|@*">
      <xsl:copy>
        <xsl:apply-templates select="node()|@*"/>
      </xsl:copy>
    </xsl:template>
    <xsl:template match="@nil">
       <xsl:attribute name="xsi:nil">
          <xsl:value-of select="."/>
       </xsl:attribute>
    </xsl:template>
</xsl:stylesheet>