CODEWITHSUNDEEP
sql-serverxmlnestedview
WyattE
WyattE

Reputation: 43

Flatten Nested XML for SQL View

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:

  1. StudyId (PK, int, not null)
  2. Name (nvarchar, not null)
  3. Objects (XML, null)

Assume the following values for 1 row:

  1. 123
  2. "A Study"
  3. See below, StackOverflow wouldn't let me use code insertion, so here it is as html...
<objects>
  <Group Name="Group1">
    <Treatment Drug="Grp1DrugA" />
    <Treatment Drug="Grp1DrugB" />
  </Group>
  <Group Name="Group2">
    <Treatment Drug="Grp2DrugC" />
  </Group>
</objects>

Desired Output:

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.

Upvotes: 0

Views: 1033

Answers (2)

swePeso
swePeso

Reputation: 1

DECLARE @data XML = N'
<objects>
  <Group Name="Group1">
    <Treatment Drug="Grp1DrugA" />
    <Treatment Drug="Grp1DrugB" />
  </Group>
  <Group Name="Group2">
    <Treatment Drug="Grp2DrugC" />
  </Group>
</objects>';

SELECT          g.n.value(N'@Name', N'NVARCHAR(16)') AS group_name,
                t.n.value(N'@Drug', N'NVARCHAR(32)') AS treatment_drug
FROM            @data.nodes(N'objects/Group') AS g(n)
CROSS APPLY     g.n.nodes(N'Treatment') AS t(n);

Upvotes: 0

Ric Guerrero
Ric Guerrero

Reputation: 66

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.

Upvotes: 2

Related Questions