Parse only specific part of XML field?

Question

Parse only specific part of XML field?

jbalbo

SSCertifiable

Points: 7583
More actions
July 1, 2020 at 7:46 pm

#3766819

Hi
I have an XML fieled that is storing a date and time range , the form its pulling from displays only the starting time
When I try to parse as Time it's not correct.
The field looks like this
<Time_x0020_of_x0020_Call>2020-06-22T09:00:00-04:00</Time_x0020_of_x0020_Call>
I want to pull 9:00AM
I tried ... data.value('(/Data/Time_x0020_of_x0020_Call)[1]', 'Time') AS TimeofCall
I know it's wrong, but not sure what to use or how to just get the starting time

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

Given that this works:

DECLARE @x AS TIME = '2020-06-22T09:00:00-04:00'

SELECT @x

you are very close.

Can you provide a full example XML string for us to test with?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 2

So the field looks like this

<Data>

<Client_x0020_Phone_x0020_Number>1-cell-phone</Client_x0020_Phone_x0020_Number>

<Time_x0020_of_x0020_Call>2020-06-22T09:00:00-04:00</Time_x0020_of_x0020_Call>

<Time_x0020_of_x0020_Disposition>2020-06-22T10:00:00-04:00</Time_x0020_of_x0020_Disposition>

<Family_x0020_Resource_x0020_Worker>famresworker</Family_x0020_Resource_x0020_Worker>

<Family_x0020_Resource_x0020_Worker_x0020_Phone>1-fam-res-phone</Family_x0020_Resource_x0020_Worker_x0020_Phone>

</Data>

So why does

SELECT

data.value('(/Data/Client_x0020_Phone_x0020_Number)[1]', 'varchar(30)') AS ClientPhone,

data.value('(/Data/Time_x0020_of_x0020_Call)[1]', 'Time') AS TimeofCall, ....

have output of "13:00:00.0000000"

thanks

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

That is weird, but if time is pressing and you want to get round it, you could do something like this:

SELECT ClientPhone = @x.value('(/Data/Client_x0020_Phone_x0020_Number)[1]', 'varchar(30)')
      ,TimeofCall = CAST(@x.value('(/Data/Time_x0020_of_x0020_Call)[1]', 'varchar(30)') AS TIME)

(Where @x is an XML variable containing your source XML.)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

jbalbo SSCertifiable Points: 7583 More actions · Answer 4

That worked great!

I just used

CAST(data.value('(/Data/Time_x0020_of_x0020_Call)[1]', 'varchar(30)') AS TIME) AS TimeofCall

Thank Again