Это старый вопрос, но все еще актуален сегодня.
Правильный способ обработки исключений при использовании oop SimpleXMLElment такой же.
libxml_use_internal_errors(TRUE); // this turns off spitting errors on your screen
try {
$xml = new SimpleXMLElement($xmlStringOfData);
} catch (Exception $e) {
// Do something with the exception, or ignore it.
}
It's a real pain. You have to switch them out before the UNPIVOT
, because there is no row produced for ISNULL()
to operate on - code generation is your friend here.
I have the problem on PIVOT
as well. Missing rows turn into NULL
, which you have to wrap in ISNULL()
all the way across the row if missing values are the same as 0.0
for example.
To preserve NULLs, use CROSS JOIN ... CASE:
select a.ID, b.column_name
, column_value =
case b.column_name
when 'col1' then a.col1
when 'col2' then a.col2
when 'col3' then a.col3
when 'col4' then a.col4
end
from (
select ID, col1, col2, col3, col4
from table1
) a
cross join (
select 'col1' union all
select 'col2' union all
select 'col3' union all
select 'col4'
) b (column_name)
Instead of:
select ID, column_name, column_value
From (
select ID, col1, col2, col3, col4
from from table1
) a
unpivot (
column_value FOR column_name IN (
col1, col2, col3, col4)
) b
A text editor with column mode makes such queries easier to write. UltraEdit has it, so does Emacs. In Emacs it's called rectangular edit.
You might need to script it for 100 columns.