How to extract in a string the date-time of a given XML field - SQL Server 2012 Query

Question

How to extract in a string the date-time of a given XML field - SQL Server 2012 Query

Navigation

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

2

Good afternoon!

Personal, in my table log has a field called logdescricao and in this field it stores several expressions. For example:

<log expressao="7085"><par traduzir="N">André Mariano da Silva</par><par traduzir="N">Gestores Boavista</par><par formatarData="S"><par traduzir="N">21/09/2017 09:19:00</par></par><par traduzir="N">Teste</par></log>

I wanted to know, how can I remove from what he brings me, only the date 21/09/2017 09:19:00. NOTE: The number of characters does not have standard sizes, so making it difficult to use a substring, for example. How can I accomplish this?

sql sql-server query xml sql-server-2012

asked by anonymous 05.09.2017 / 19:38

2 answers

Update does not update bank records How to Suggest name when saving report Crystal Report

score 2 · Answer 1

From what I saw the date is inside another "par" tag, which can be selected with the code below.

 DECLARE @myDoc xml 

SET @myDoc = '<log expressao="7085">
<par traduzir="N">André Mariano da Silva</par>
<par traduzir="N">Gestores Boavista</par>
<par formatarData="S">
<par traduzir="N">21/09/2017 09:19:00</par>
</par>
<par traduzir="N">Teste</par>
</log>'  

    select  @myDoc.query('(/log/par/par)')

If you want to return only the value, use the value ()

SELECT @myDoc.value('(/log/par/par)[1]', 'varchar(30)')

score 1 · Answer 2

If your string contains only one DATA and that date was within your <par formatarData= , you can use CHARINDEX to check what position it is in and get it through SUBSTRING .

declare @texto varchar(1000) = '<log expressao="7085"><par traduzir="N">André Mariano da Silva</par><par traduzir="N">Gestores Boavista</par><par formatarData="S"><par traduzir="N">21/09/2017 09:19:00</par></par><par traduzir="N">Teste</par></log'

select SUBSTRING(@texto, CHARINDEX('<par formatarData=',@texto) + 40, 19)

Now if you have more than one DATA, then theoretically you would need a loop to check the position of the next date.