CODEWITHSUNDEEP
sql-servert-sqlxquery
Margus
Margus

Reputation: 20038

SQL Server query empty xml element value

I had empty instance that was breaking the query 

...
<publicationreference>
  ...
  <referencedpublicationid />

Before query contained :

reference.value(
  './referencedpublicationid[1]','uniqueidentifier') 
  AS [referencedpublicationid],

resulting in error.

After fix query contained :

reference.value(
  '(./Field[@fieldName="referencedpublicationid"]/text())[1]','uniqueidentifier') 
  AS [referencedpublicationid],

resulting in <referencedpublicationid xsi:nil="true" />

Can someone explain how does those lines differ?

Upvotes: 1

Views: 1944

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

It looks like your two queries are against different XML sources. In the first you have referencedpublicationid as a element name and in the second you have it as an attribute called @fieldName. I also have a hard time understanding what the resulting XML has to do with this. xsi:nil="true" is generated when you create XML using for xml path, elements xsinil.

I will instead try to address what I think is the issue you see.

Try this code:

declare @XML xml = '<referencedpublicationid />'
select @XML.value('./referencedpublicationid[1]', 'nvarchar(50)')
select @XML.value('(./referencedpublicationid/text())[1]', 'nvarchar(50)')

The first query returns an empty string and the second returns NULL.

If you instead specify the data type uniqueidentifier the first will fail because you can not cast an empty string to uniqueidentifier. The second converts fine since it is NULL.

All values in untyped XML in SQL Server are strings that is then converted to the specified data type in the values function.

When you use ./referencedpublicationid[1] you will get back the concatenated value of all sub text() values. SQL Server for some reason gives you an empty string when there are no text() nodes to be found.

This expression (./referencedpublicationid/text())[1] specifies the first text() node in referencedpublicationid and since there are none you get the result of NULL.

Upvotes: 3

Related Questions