CODEWITHSUNDEEP
sqlsql-serversqlxmlxquery-sql
Kirill V. Lyadvinsky
Kirill V. Lyadvinsky

Reputation: 99685

How to check that the XML element has the attribute with an empty string value

Is there a way to check that value of an element in the XML field has an empty size using SQLXML? Consider I have the following data in the column Conf of the table Test:

<Conf>
  <UserData>
    <data type="str" value="" />
  </UserData>
</Conf>

I can check that data exists by using the following SQL request:

SELECT Test.Conf.exist('/Conf/UserData/data') FROM Test;

But how can I check that data has an empty value? It could be something like the following, but it doesn't work:

SELECT Test.Conf.value('(/Conf/UserData/data/@value)[1]', 'nvarchar(max)')='' FROM Test;

My final solution is to use the following SQL statement:

SELECT Test.Conf.value('string-length(/Conf[1]/UserData[1]/data[1]/@value)', 'int') FROM Test;

Upvotes: 2

Views: 15606

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

The problem is not with XPath but with TSQL syntax.

XML.Exist returns a BIT datatype, indicating whether it exists. SQL Server has no native BOOLEAN datatype (a real true/false).

So, your query should have been

SELECT CASE WHEN Test.Conf.value('(/Conf/UserData/data/@value)[1]', 'nvarchar(max)')=''
       THEN 1 ELSE 0 END
FROM Test;

Upvotes: 0

Renaud
Renaud

Reputation: 8873

Using XPath 1.0. string(@someattribute) test should return false if empty. I know nothing about SQLXML, but it will work if you can use a control sequence.

Upvotes: 1

Jonathan
Jonathan

Reputation: 1719

Possibly this will work.

SELECT Test.Conf.exist('data(/Conf/UserData/data[@value=''''])') FROM Test;

This checks to see if a data element with @value = '' exists.

Upvotes: 1

Related Questions