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
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.
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
You 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
