Sql Server - For XML - Get null value from element
<p>I am trying to replace the null xml element into the null value while doing the concatenation. And i am making some silly mistake. I want to differentiate between an empty value and null value. I am using OpenXML to parse the XML data and something is missing in the code to read the null based param element.</p>

<p>I am using Server Server 2014. </p>

<p>Please suggest.</p>

<pre><code>DECLARE @message_body XML;
DECLARE @XMLParameterData Table 
                        (SeqID INT Identity(1,1), 
                         ParamValue varchar(max))

DECLARE @docRef int
DECLARE @dataPath nvarchar(255) 
DECLARE @mappingType int = 2  --Element-Centric mapping

Select   @message_body = N'&lt;AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"&gt;
                                                                    &lt;ParamList&gt;                                                                 &lt;Param&gt;Bruce&lt;/Param&gt;
                                                                    &lt;Param&gt;Wa''yne&lt;/Param&gt;
                                                                    &lt;Param&gt;Bruce@karan.com&lt;/Param&gt;
                                                                    &lt;Param&gt;Coke&lt;/Param&gt;
                                                                    &lt;Param&gt;20000&lt;/Param&gt;                                                                    
                                                                    &lt;Param xsi:nil="true"/&gt;
                                                                    &lt;Param&gt;&lt;/Param&gt;
                                                                &lt;/ParamList&gt;
                                                    &lt;/AsyncRequest&gt;';


Set @dataPath = '/AsyncRequest/ParamList/Param'
EXEC sp_xml_preparedocument @docRef output, @message_body

INSERT INTO @XMLParameterData(ParamValue)
        Select * From OpenXML(@docRef, @dataPath, @mappingType)
                    WITH
                            (                               
                                valx varchar(max) '.'
                            )

-- the xml document ref needs to be released ASAP       
EXEC sp_xml_removedocument @docRef  

SELECT * From @XMLParameterData
DECLARE @CSVString varchar(max)
SELECT  @CSVString = STUFF( 
                            (SELECT ', ' +  
                            CHAR(34) + ParamValue + CHAR(34)
                             FROM @XMLParameterData
                             ORDER BY SeqID
                             FOR XML PATH('')                                
                             ), 1, 1, '')

SELECT  @CSVString as CSVTest
</code></pre>

<p>Output :- 
 "Bruce", "Wa'yne", "Bruce@karan.com", "Coke", "20000", "", ""</p>

<p><strong>Desired output :-</strong> 
 "Bruce", "Wa'yne", "Bruce@karan.com", "Coke", "20000", <strong>NULL</strong>, ""</p>