CODEWITHSUNDEEP
sqlsql-serverxmlxml-parsingsqlxml
Prosper
Prosper

Reputation: 98

Prevent XML empty tag parsing to zero

I have an XML parameter with empty tag like this.

<dietTypes><dietType><dietaryID></dietaryID></dietType></dietTypes>

I am trying to parse it to an integer like this in a stored procedure,

DECLARE @MemDietTypes TABLE(DietaryID INT);
INSERT INTO @MemDietTypes
SELECT  DietTypes.Col.value('dietaryID[1]', 'INT')
FROM   @DietaryTypeXML.nodes('//dietType') DietTypes(Col);

The above is parsed as zero, which is problematic for me. How can I prevent this happening? Thanks

Upvotes: 0

Views: 478

Answers (3)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

Try this to understand the implicit defaults:

SELECT CAST(NULL AS INT)
      ,CAST('' AS INT)
      ,CAST('      ' AS INT);

A NULL remains unchanged, but empty is defaulted to a zero. That is the designed behaviour.

Now back to your issue:

DECLARE @DietaryTypeXML XML='<dietTypes>
                               <dietType>
                                 <dietaryID></dietaryID> <!-- This is empty -->
                               </dietType>
                             </dietTypes>';

--The engine gets an empty value and defaults it to a zero.
--If you use varchar instead, you get a blank, but you'll have to deal with string-typed numbers.

SELECT  DietTypes.Col.value('dietaryID[1]', 'INT') AS Empty_in_INT_is_implicitly_taken_as_zero
       ,DietTypes.Col.value('dietaryID[1]', 'VARCHAR(100)') AS Empty_in_VARCHAR_is_implicitly_taken_as_empty_string
FROM   @DietaryTypeXML.nodes('//dietType') DietTypes(Col);

--The solution I suggest:

SELECT  DietTypes.Col.value('dietaryID[1] cast as xs:int?', 'INT') 
FROM   @DietaryTypeXML.nodes('//dietType') DietTypes(Col);

Using the XQuery cast as xs:int? tells the engine, that the given value is a nullable int. Now the value returned is no more empty but NULL.

Upvotes: 2

roman
roman

Reputation: 117337

you can use text() function:

declare @data xml = '
    <dietTypes><dietType><dietaryID></dietaryID></dietType><dietType><dietaryID>2</dietaryID></dietType></dietTypes>';

select
    t.c.value('(dietaryID/text())[1]', 'int')
from @data.nodes('//dietType') as t(c);

returns:

dietaryID
-----------
NULL
2

Upvotes: 1

Nitika
Nitika

Reputation: 463

enter image description hereYou need to change datatype of DietaryID to varchar()

declare @xml xml='<dietTypes>
<dietType>
<dietaryID></dietaryID>
</dietType>
</dietTypes>'

DECLARE @MemDietTypes TABLE(DietaryID varchar(10));
INSERT INTO @MemDietTypes
SELECT  DietTypes.Col.value('dietaryID[1]', 'VARCHAR(10)')
FROM   @xml.nodes('//dietTypes/dietType') DietTypes(Col);

select * from @MemDietTypes

Upvotes: 0

Related Questions