Karan Karan - 2 months ago 5
SQL Question

Sql Server - For XML - Get null value from element

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.

I am using Server Server 2014.

Please suggest.

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'<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ParamList> <Param>Bruce</Param>
<Param>Wa''yne</Param>
<Param>Bruce@karan.com</Param>
<Param>Coke</Param>
<Param>20000</Param>
<Param xsi:nil="true"/>
<Param></Param>
</ParamList>
</AsyncRequest>';


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


Output :-
"Bruce", "Wa'yne", "Bruce@karan.com", "Coke", "20000", "", ""

Desired output :-
"Bruce", "Wa'yne", "Bruce@karan.com", "Coke", "20000", NULL, ""

Answer

Keep it simple! Use CASE WHEN to check if @xsi:nil="true" and .nodes instead of OPENXML:

DECLARE @message_body XML,
        @output nvarchar(max);

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

SELECT @output = STUFF((
    SELECT 
            CASE WHEN t.v.value('@xsi:nil','nvarchar(max)') = 'true' THEN ',NULL' 
                    ELSE ',"'+t.v.value('.','nvarchar(max)') + '"' 
                        END
    FROM @message_body.nodes('AsyncRequest/ParamList/Param') as t(v)
    FOR XML PATH('')
),1,1,'')

SELECT @output 

Will return:

"Bruce","Wa'yne","Bruce@karan.com","Coke","20000",NULL,""