XML formatado

Necessitei criar um arquivo formatado:

select  '<doc>''||E'\n'
                '<field name="cd_email">'|| cd_email ||'</field>'||E'\n'||
                '<field name="ds_remetente_email">'|| ds_remetente_email ||'</field>'||E'\n'||
                '<field name="ds_destinatario_email">'|| ds_destinatario_email ||'</field>'||E'\n'||
                '<field name="ds_copia">'|| ds_copia ||'</field>'||E'\n'||
                '<field name="ds_assunto">'|| ds_assunto ||'</field>'||E'\n'||
                '<field name="ds_mensagem_txt">'|| strip_tags(ds_mensagem_txt) ||'</field>'||E'\n'||
                '<field name="dt_abertura">'|| dt_abertura ||'</field>'||E'\n'||
                '<field name="dt_envio">'|| dt_envio ||'</field>'||E'\n'||
                '<field name="dt_recebimento">'|| dt_recebimento ||'</field>'||E'\n'||
        '<doc>'||E'\n'||E'\n'FROM crm.tb_email;

A solução acima gera um trabalho muito grande para escrever a consulta.

Com a expressão abaixo tem-se:

select  'xmlelement(name field,  xmlattributes(''' || coluna  || ''' as name), ' ||coluna|| '),' from
(select  a.attname as coluna
from pg_catalog.pg_attribute a inner join pg_stat_user_tables c on a.attrelid = c.relid
WHERE a.attnum > 0
and NOT a.attisdropped
and c.relname = 'tb_email') as foo

o seguinte resultado:

 xmlelement(name field,  xmlattributes('cd_email' as name), cd_email),
 xmlelement(name field,  xmlattributes('cd_caixa_email' as name), cd_caixa_email),
 xmlelement(name field,  xmlattributes('cd_usuario_cadastro' as name), cd_usuario_cadastro),
 xmlelement(name field,  xmlattributes('cd_usuario_abertura' as name), cd_usuario_abertura),
 xmlelement(name field,  xmlattributes('cd_usuario_responsavel' as name), cd_usuario_responsavel),
 xmlelement(name field,  xmlattributes('cd_status_documento' as name), cd_status_documento),
 xmlelement(name field,  xmlattributes('cd_status_fluxo' as name), cd_status_fluxo),
 xmlelement(name field,  xmlattributes('ds_remetente_email' as name), ds_remetente_email),
 xmlelement(name field,  xmlattributes('ds_destinatario_email' as name), ds_destinatario_email),
 xmlelement(name field,  xmlattributes('ds_copia' as name), ds_copia),
 xmlelement(name field,  xmlattributes('ds_assunto' as name), ds_assunto),
 xmlelement(name field,  xmlattributes('ds_mensagem' as name), ds_mensagem),
 xmlelement(name field,  xmlattributes('nu_protocolo_email' as name), nu_protocolo_email),
 xmlelement(name field,  xmlattributes('ds_mensagem_txt' as name), ds_mensagem_txt),
 xmlelement(name field,  xmlattributes('ds_mensagem_srch' as name), ds_mensagem_srch),
 xmlelement(name field,  xmlattributes('dt_abertura' as name), dt_abertura),
 xmlelement(name field,  xmlattributes('dt_envio' as name), dt_envio),
 xmlelement(name field,  xmlattributes('dt_recebimento' as name), dt_recebimento),
 xmlelement(name field,  xmlattributes('dt_auditoria' as name), dt_auditoria),

com o resultado pode-se criar uma nova consulta

select '< doc >',

 xmlelement(name field,  xmlattributes('cd_email' as name), cd_email),

 xmlelement(name field,  xmlattributes('cd_caixa_email' as name), cd_caixa_email),

 xmlelement(name field,  xmlattributes('cd_usuario_cadastro' as name), cd_usuario_cadastro),

 xmlelement(name field,  xmlattributes('cd_usuario_abertura' as name), cd_usuario_abertura),

 xmlelement(name field,  xmlattributes('cd_usuario_responsavel' as name), cd_usuario_responsavel),

 xmlelement(name field,  xmlattributes('cd_status_documento' as name), cd_status_documento),

 xmlelement(name field,  xmlattributes('cd_status_fluxo' as name), cd_status_fluxo),

 xmlelement(name field,  xmlattributes('ds_remetente_email' as name), ds_remetente_email),

 xmlelement(name field,  xmlattributes('ds_destinatario_email' as name), ds_destinatario_email),

 xmlelement(name field,  xmlattributes('ds_copia' as name), ds_copia),

 xmlelement(name field,  xmlattributes('ds_assunto' as name), ds_assunto),

 xmlelement(name field,  xmlattributes('ds_mensagem' as name), ds_mensagem),

 xmlelement(name field,  xmlattributes('nu_protocolo_email' as name), nu_protocolo_email),

 xmlelement(name field,  xmlattributes('ds_mensagem_txt' as name), ds_mensagem_txt),

 xmlelement(name field,  xmlattributes('ds_mensagem_srch' as name), ds_mensagem_srch),

 xmlelement(name field,  xmlattributes('dt_abertura' as name), dt_abertura),

 xmlelement(name field,  xmlattributes('dt_envio' as name), dt_envio),

 xmlelement(name field,  xmlattributes('dt_recebimento' as name), dt_recebimento),

 xmlelement(name field,  xmlattributes('dt_auditoria' as name), dt_auditoria),

'< / doc >'

from crm.tb_email

Consulta XML

A função xmlforest() do postgresql emite uma saida em XML

select xmlforest(ibge,sigla,nome) from estados where sigla='PR'

"41PRParaná"

XML to Array PHP

Contribuição de Ricardo Feitosa (Transforma um XML para array)

function xml2array($xml)

{

$xmlary = array();

$reels = '/<(\w+)\s*([^\/>]*)\s*(?:\/>|>(.*)<\/\s*\\1\s*>)/s';

$reattrs = '/(\w+)=(?:"|\')([^"\']*)(:?"|\')/';

preg_match_all($reels, $xml, $elements);

if (is_array($elements))

{

foreach ($elements[1] as $ie => $xx)

{

$cdend = strpos($elements[3][$ie], "<");

if ($cdend > 0)

{

$xmlary[$elements[1][$ie]] = substr($elements[3][$ie], 0, $cdend - 1);

}

if (preg_match($reels, $elements[3][$ie]))

$xmlary[$elements[1][$ie]] = xml2array($elements[3][$ie]);

else

if ($elements[3][$ie])

{

$xmlary[$elements[1][$ie]] = $elements[3][$ie];

}

}

return $xmlary;

}

return $xml;

}

function xml2get($id,$xml)

{

$parametros = xml2array($xml);

$resultado = "";

foreach ($parametros[$id] as $key => $value)

$resultado.= "$key=".urlencode($value)."&";

if (substr($resultado,-1) == "&")

return substr($resultado,0,-1);

return false;

}

Leitura XML em PHP

$filename ="/windows/discog/wwwroot/xml/questionario.xml";

$xml = simplexml_load_file($filename );

if (!$xml) {
$errors = libxml_get_errors();
foreach ($errors as $error) {
echo display_xml_error($error, $xml);
}
libxml_clear_errors();
}

var_dump($xml);
var_dump(get_object_vars($xml));

?>