CODEWITHSUNDEEP
sqlxml
Nathan
Nathan

Reputation: 1520

How to check if XML has value in SQL?

I have a simple problem with XML got from database. Now, this XML can have no values inside it, as follows:

 <TryXML xmlns="">
 <TryXML_XML_VERSION>1.0</TryXML_XML_VERSION>
 <TITLE_DESC />
 <ISCI_CODE />
 <PRODUCT_DESC />
 <PLAY_LENGTH />
 <START_TIME />
 <COMPRESSION_LEVEL_CODE />
 <ENCODER_TYPE_DESC />
 <OUTPUT_RATE />
 <FRAME_RATE />
 <FILE_NAME />
 <FILE_SIZE />
 <COPY_DATE />
 <ADVERTISER />
 <AGENCY_VENDOR />
 </TryXML>

or it could have values inside, like this

<TryXML xmlns="">
<TryXML_XML_VERSION>1.0</TryXML_XML_VERSION>
<TITLE_DESC>asd</TITLE_DESC>
<ISCI_CODE>asd</ISCI_CODE>
<PRODUCT_DESC>sd</PRODUCT_DESC>
<PLAY_LENGTH>asdklgh</PLAY_LENGTH>
<START_TIME>sdghkl</START_TIME>
<COMPRESSION_LEVEL_CODE />
<ENCODER_TYPE_DESC />
<OUTPUT_RATE />
<FRAME_RATE />
<FILE_NAME>sdfgjkl</FILE_NAME>
<FILE_SIZE />
<COPY_DATE />
<ADVERTISER>sdfgklj</ADVERTISER>
<AGENCY_VENDOR>sdfgjkl</AGENCY_VENDOR>
</TryXML>

How can I check if the said XML nodes has values or not? NOTE: The values can be anything alpha-numeric.

Upvotes: 3

Views: 11157

Answers (2)

Andomar
Andomar

Reputation: 238086

Assuming you're using SQL Server, you can use value:

declare @t table (col1 xml)
insert @t values ('<root><a>val</a></root>')
insert @t values ('<root><a></a></root>')
insert @t values ('<root><a/></root>')
insert @t values ('<root></root>')

select  *
from    @t
where   col1.value('(/root/a)[1]','varchar(50)') <> ''

This only shows the first row, where /root/a has a value. As requested in the comment an example with a case:

select  case 
        when col1.value('(/root/a)[1]','varchar(50)') is null then 'Null' 
        else 'Not Null' 
        end
from    @t

Example at SE.Data.

Upvotes: 4

Aydin K.
Aydin K.

Reputation: 3367

If the question is, how to recognize (and distinguish between) "null"-fields and fields with empty strings, you should consider a redesign of your database-tables and export-script:

1) Introduce "null"-fields in your database table(s).

2)

  • If a field has a "null" value, don't export this field at all.

  • If a field has an empty string, export it - like you're doing it now.

Upvotes: 0

Related Questions