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

Asked

Viewed 395 times

2

Good afternoon!

Guys, in my log table there is 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 wonder, how can I withdraw from what it 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 do this?

sql sql-server xml query sql-server-2012

2 answers

2

From what I saw the date is inside another "pair" 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 function value()

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

  • Just add the information that if you have any other tags with par inside par won’t work.

  • My friend @Alexandre Cavaloti, well, this consultation worked, however, only on the fixed date q you set, however, I have other records in this same scheme, however, the date changes constantly, I mean, I have other records that return today’s date, for example. How to return the other records by returning only the date?

  • In the last line I added only the date is returned. The number [1] indicates that you are returning the first node in this situation.

  • 1

    Consider the code and not necessarily the default, if the date is always on a second level of <par> can consider this pattern if it may not be necessary to increment Xquery with a filter. The important thing is to understand the pattern or attributes to filter.

1

If your string contains only one DATE and that date was inside your <par formatarData=, you can use the CHARINDEX to check the position it is in and get it through the 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.

Browser other questions tagged sql sql-server xml query sql-server-2012

You are not signed in. Login or sign up in order to post.