How do I check if XML is relevant in SQL?
I have a simple problem with XML retrieved from a database. Now this XML cannot have any values inside it:
<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 can have values internally like
<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 specified XML nodes have values or not? NOTE. The values can be any alphanumeric.
source to share
Assuming you are 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 line where /root/a
it matters. As requested in the comment, an example with case
:
select case
when col1.value('(/root/a)[1]','varchar(50)') is null then 'Null'
else 'Not Null'
end
from @t
source to share
If the question is how to recognize (and distinguish) "null" fields and fields with empty strings, you should consider redesigning your database tables and export-script:
1) Enter the fields "null" in your database table (s).
2)
-
If a field has a "null" value, do not export that field at all.
-
If the field has an empty string, export it - as you are doing now.
source to share