TSQL XQUERY Question - Not sure if it's my XQUERY syntax or poorly formed XML thats the issue

Question

I'm not sure if it's my XQUERY code here or that the XML column's data is poorly formed.

-- drop table #tmp

create table #tmp ([id] varchar(20), [type]     varchar(100), [name] varchar(100), [item] XML)
go

insert into #tmp ([id], [type], [name], [item])
values
('1', 'APP', 'type1', '<appdata            source="applicationdata" name="name1" value="FlowRate"     units="people/h" />')
    go
insert into #tmp ([id], [type], [name], [item])
values
('2', 'APP', 'type2', '<appdata source="applicationdata" name="name2" value="FlowRate" units="people/h" />')
go
insert into #tmp ([id], [type], [name], [item])
values
('3', 'APP', 'type3', '<appdata source="applicationdata" name="name3" value="FlowRate" units="people/h" />')
go

-- select * from #tmp 

-- attempt 1
select tt.[id], tt.[type], tt.[name],
C.value('source','varchar(100)') as [source],
C.value('name','varchar(100)') as [name],
C.value('value','varchar(100)') as [value],
C.value('units','varchar(100)') as [units]
from #tmp tt
cross apply 
item.nodes('/appdata') as T(C)

-- atempt 1 result
-- singleton error

-- attempt 2
select tt.[id], tt.[type], tt.[name],

C.query('source').value('.', 'varchar(1000)') as     [source],
C.query('name').value('.', 'varchar(1000)') as [name],
C.query('value').value('.', 'varchar(1000)') as [value],
C.query('units').value('.', 'varchar(1000)') as     [units]

from #tmp tt
cross apply 
item.nodes('/.') as T(C)
outer apply C.nodes ('appdata') as S(D)

-- attempt 2 result
-- xml column values don't shred

The contents of the XML column doesn't have an element escape tag and the contents all seem to be added in as attributes. Is XQUERY able to process "XML" formed like this or do i need to manipulate it?

FYI, I was able to return data using a single XML string and the sp_xml_preparedocument SP, but i really want to shred these attributes out into separate columns of the same table.

Answer 1

If there is only one <appdata> element per row, you can omit .nodes() and call this like here:

select tt.[id], tt.[type], tt.[name],
tt.item.value('(/appdata/@source)[1]','varchar(100)') as [source],
tt.item.value('(/appdata/@name)[1]','varchar(100)') as [name],
tt.item.value('(/appdata/@value)[1]','varchar(100)') as [value],
tt.item.value('(/appdata/@units)[1]','varchar(100)') as [units]
from #tmp tt;

This would be slightly faster...

If performance doesn't matter, go with deep patel's answer, which is a little easier to read IMO...

Answer 2

select tt.[id], tt.[type], tt.[name],
C.value('@source','varchar(100)') as [source],
C.value('@name','varchar(100)') as [name],
C.value('@value','varchar(100)') as [value],
C.value('@units','varchar(100)') as [units]
from #tmp tt
cross apply 
item.nodes('/appdata') as T(C)