SQL query filter

Question

SQL query filter

Navigation

#1 by (3 votes)
#2 by (3 votes)

1

I have a field called emailMSG0 inside a table in the database. This field contains various information. Is there any possibility in SQL to filter what I want to get inside this emailMSG0 field? Bring just some information.

If I make a normal select for example:

SELECT emailMSG0 FROM TABELA

Return this to me:

Prezado Cliente Pedido: 4264136, NF: 1448692 Pedido: 4264138, NF: 1448693 Pedido: 4264140, NF: 1448694 Data: 2015-12-08 10:37:49 Hora: 2015-12-08 10:37:49 Link do comprovante: <img src='cid:1krrpu5mb4br3'/>

Is there a way to filter what I want to appear? I just want to return the requests from this field.

sql-server select

asked by anonymous 19.05.2016 / 13:51

2 answers

3

If you want to get only part of the content of a field that holds XML, it is best to use an external SQL application to handle that information. You can do with SQL, but it is not the most appropriate tool for several reasons:

SQL Server is poor in APIs to work with XML;
Even if it were not, the Transact-SQL language is procedural. Any code to handle XML is probably much longer and more complex than if you used an object-oriented language, for example;
If you are saving XML with a well-defined schema in the database, you may need to rethink your application to transform the XML data into a more standardized form to store in the database.

If you still want to go ahead, you can use XQuery . It is supported from SQL Server 2008. The link here is the official documentation, translated by PT-br engine. I suggest you take a look at the documentation in English if you are fluent.

19.05.2016 / 14:14

List, virtual and Entity Framework Refresh Activity prior to ending Current Activity

score 3 · Accepted Answer

The rough way in PHP would be more or less what you would do as the database return

  <?php
        $retorno="Prezado Cliente <b></b><br><br>Pedido:<b>4544</b>NF:<b>4441</b>Pedido:<b>4543</b> NF:<b>4442</b>Pedido:<b>4546</b>NF:b>4443</b>Pedido:<b>4545</b>NF:<b>4444</b>Pedido:<b>4547</b>NF:<b>4445</b>";

        $teste = explode('Pedido:',strip_tags(explode('Data:', $retorno)[0], "<b></b><br>"));
        for($i =1; $i < sizeof($teste); $i++){
            $ped_nf = explode("NF:", $teste[$i]);
            echo ' Pedido: '. $ped_nf[0].' NF: '.$ped_nf[1];
        }
        //print_r($teste);
?>

Some adjustments may be necessary, but this is one of the paths.

Two explodes are given in the string to separate the part of the requests and already removing the HTML tags.
Already begins to traverse the array in index 1, because position 0 only remains the name of the Client.
Then another explodes to separate NF requests and is life following. Hope it helps.