Summary:
Given a SQL Server db table that contains columns of various types, one being an XML column, I need to digest the XML for each row and present this data in a view.
Assume the following table for simplicity:
Table name: Study
Columns:
Assume the following values for 1 row:
<objects>
<Group Name="Group1">
<Treatment Drug="Grp1DrugA" />
<Treatment Drug="Grp1DrugB" />
</Group>
<Group Name="Group2">
<Treatment Drug="Grp2DrugC" />
</Group>
</objects>
Desired Output:
Note:
Here are a couple of the numerous approaches I've tried for this, I can list more if that would be helpful.
Most posts I've read on these sort of operations involve a declared xml variable. My understanding is that you cannot create/use variables inside a view, so I'm a bit stuck.
Approach 1:
select stud.StudyId,
stud.Name as 'StudyName',
tbl.treatment.value('(../Group/@Name)[1]','varchar(30)') as 'Group',
tbl.treatment.value('(@Drug)[1]', 'varchar(30)') as 'Drug'
from dbo.Study stud
cross apply stud.StudyObjects.nodes('//Group/Treatment') as tbl(treatment)
This gives me nothing in my Group column.
Approach 2:
select stud.StudyId,
stud.Name as 'StudyName',
grp.value('(@Name)[1]', 'varchar(30)') as 'Group',
treatment.value('(@Drug)[1]', 'varchar(30)') as 'Drug'
from dbo.Study stud
cross apply stud.StudyObjects.nodes('//Group') as table1(grp)
cross apply grp.nodes('//Group/Treatment') as table2(treatment)
This results in every combination of groups/treatments.
I tried this way
SELECT
StudyId
,Name
,o.value('../@Name', 'varchar(30)') [Group]
,o.value('@Drug', 'varchar(30)') Drug
FROM Study S
CROSS APPLY S.[Objects].nodes('objects/Group/Treatment') xmlData(o)
Got this result
StudyId Name Group Drug
----------- ---------- ------------ ------------
123 A Study Group1 Grp1DrugA
123 A Study Group1 Grp1DrugB
123 A Study Group2 Grp2DrugC
Hope it helps.