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

Question

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;

Renaud · Accepted Answer · 2011-03-11 20:56:38Z

1

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.

answered Mar 11, 2011 at 20:56

Renaud

8,8834 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirill V. Lyadvinsky Over a year ago

string-length function does the job.

Jonathan · Accepted Answer · 2011-03-11 20:56:08Z

1

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.

answered Mar 11, 2011 at 20:56

Jonathan

1,7191 gold badge15 silver badges19 bronze badges

Comments

RichardTheKiwi · Accepted Answer · 2011-03-11 22:19:22Z

0

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;

answered Mar 11, 2011 at 22:19

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related