Parse only specific part of XML field?

Question

Parse only specific part of XML field?

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

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

Phil Parkin SSC Guru Points: 247183 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?

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: 247183 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.)

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