Select XML attribute

1

Dears,

I had a sql table with an xml field as mentioned below. Please let me know how can I retrieve the values of xml nodes with a SQL query.

Thanks in advance.

Sijesh

2

Hello

Please provide sample xml in the form of text? You can post by using these three ticks ``` in front and at the end of the xml

6

Dear Yosiasz,

Please find the xml text below.

'''<EmailParameter> <Intime Value="9:00 AM" /> <OutTime Value="5:00 PM" /> </EmailParameter>'''

Thanks

7

here is one way

use sqlteam
go

declare @data table(mojo xml)

insert into @data
select '<EmailParameter> <Intime Value="9:00 AM" /> <OutTime Value="5:00 PM" /> </EmailParameter>'


select i.t.value('@Value', 'VARCHAR(100)') as InTime,
       o.t.value('@Value', 'VARCHAR(100)') as InTime
        
  from @data as d
 CROSS APPLY d.mojo.nodes('//EmailParameter/Intime') as i(t)
 CROSS APPLY d.mojo.nodes('//EmailParameter/OutTime') as o(t)

--or as time

select i.t.value('@Value', 'time') as InTime,
       o.t.value('@Value', 'time') as InTime
        
  from @data as d
 CROSS APPLY d.mojo.nodes('//EmailParameter/Intime') as i(t)
 CROSS APPLY d.mojo.nodes('//EmailParameter/OutTime') as o(t)
1 Like
8

Dear Yosiasz,

Thank you very much.

9

or

select  d.mojo.value('EmailParameter[1]/Intime[1]/@Value', 'varchar(max)'),
        d.mojo.value('EmailParameter[1]/OutTime[1]/@Value', 'varchar(max)')
  from @data as d