CODEWITHSUNDEEP
sqlsql-server
Karan
Karan

Reputation: 3338

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>[email protected]</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", "[email protected]", "Coke", "20000", "", ""

Desired output :- "Bruce", "Wa'yne", "[email protected]", "Coke", "20000", NULL, ""

Upvotes: 3

Views: 1914

Answers (3)

Paweł Dyl
Paweł Dyl

Reputation: 9143

What are you trying to achieve is not a standard behavior. You propbably expect following:

DECLARE @message_body XML = N'<AsyncRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <ParamList>
    <Param>Bruce</Param>
    <Param>Wa''yne</Param>
    <Param>[email protected]</Param>
    <Param>Coke</Param>
    <Param>20000</Param>                                                                    
    <Param xsi:nil="true"/>
    <Param></Param>
  </ParamList>
</AsyncRequest>';

SELECT X.value('.[not(@xsi:nil="true")]', 'nvarchar(MAX)') Value
FROM @message_body.nodes('//Param') T(X)

Which yields:

Value
-----
Bruce
Wa'yne
[email protected]
Coke
20000
NULL
(empty string here)

You may want text from nodes, which is more standarized:

SELECT X.value('text()[1]', 'nvarchar(MAX)') Value
FROM @message_body.nodes('//Param') T(X)

Value
-----
Bruce
Wa'yne
[email protected]
Coke
20000
NULL
NULL

Note that <element/> and <element></element> are synonyms. It's empty, no matter how you write. Ask yourself: is first <element/> empty string? That would lead to long discussion - it's all a matter of interpretation. You may also consider xml:space attribute to handle whitespaces.

Upvotes: 0

gofr1
gofr1

Reputation: 15997

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>[email protected]</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","[email protected]","Coke","20000",NULL,""

Upvotes: 2

Steve Ford
Steve Ford

Reputation: 7763

How about this (I have slightly simplified your code by using xml.nodes rather than an xml document).

It uses the xml query expression .[not(@xsi:nil = "true")] to return a null where xsi:nil is true.

I then use COALESCE to return the string 'NULL' when a NULL is returned:

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


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


INSERT INTO @XMLParameterData(ParamValue)
SELECT  T.c.value('.[not(@xsi:nil = "true")]', 'varchar(max)') AS result
FROM @message_body.nodes('/AsyncRequest/ParamList/Param')T(c)

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

SELECT  @CSVString as CSVTest

This returns: "Bruce", "Wa'yne", "[email protected]", "Coke", "20000", "NULL", ""

Upvotes: 2

Related Questions