June 26, 2018 at 12:58 am
Hi,
I'm really struggling with this query.
The purpose is to get an XML which has the format of an existing XML so I cannot deviate in structure. Now, that xml is created in code but very slow.
So I thought, since Sql is very performant, why not create the XML directly in the Sql server?
All is well, except that I get my 'sub' nodes in an outer 'sub' node. Same thing for the 'unrelated' node.
How can I get the result without the outer 'sub' and outer 'unrelated' node?
(This query is made up to enable everyone to get the same result but reflects the real problem)
Thanks sincerely
declare @head1 table
(head_id uniqueidentifier)
declare @sub1 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @sub2 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @unrelated table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @gd uniqueidentifier;
set @gd = newid();
insert into @head1 (head_id) values (@gd)
insert into @sub1 (head_id,own_id,own_field) values (@gd,10,'Sub1 field')
insert into @sub2 (head_id,own_id,own_field) values (@gd,11,'Sub2 field')
insert into @unrelated (head_id,own_id,own_field) values (@gd,100,'another field')
select alldata.* from
(select head.head_id
,(
select * from
(
select sub1.own_id,sub1.own_field
from @sub1 sub1
where sub1.head_id = head.head_id
union all
select sub2.own_id,sub2.own_field
from @sub2 sub2
where sub2.head_id = head.head_id
) tmp for xml path('sub'), type ) sub
,(select unrelated.own_id,unrelated.own_field
from @unrelated unrelated where unrelated.head_id = head.head_id
for xml path('unrelated'),type
) unrelated
from @head1 head
) alldata for xml auto,root('master')
June 26, 2018 at 5:20 am
Can you post the expected results please?
😎
June 26, 2018 at 5:25 am
Sure,
This is what the quey returns now :
<master>
<alldata head_id="C6DF494E-4DA7-4726-8288-BB463CB96834">
<sub>
<sub>
<own_id>10</own_id>
<own_field>Sub1 field</own_field>
</sub>
<sub>
<own_id>11</own_id>
<own_field>Sub2 field</own_field>
</sub>
</sub>
<unrelated>
<unrelated>
<own_id>100</own_id>
<own_field>another field</own_field>
</unrelated>
</unrelated>
</alldata>
</master>
And this is what I would like :<master>
<alldata head_id="C6DF494E-4DA7-4726-8288-BB463CB96834">
<sub>
<own_id>10</own_id>
<own_field>Sub1 field</own_field>
</sub>
<sub>
<own_id>11</own_id>
<own_field>Sub2 field</own_field>
</sub>
<unrelated>
<own_id>100</own_id>
<own_field>another field</own_field>
</unrelated>
</alldata>
</master>
(so minus the outer 'sub' and 'unrelaed' node)
Thx 😉
June 26, 2018 at 6:21 am
The solution is to use FOR XML PATH('') inside the sub-queries and alias the selection of those.
😎
June 26, 2018 at 6:37 am
Alas, the resutl I get then is thi<master>
<alldata head_id="6DBD64CD-44CE-425C-A105-BE1900299550">
<sub>
<own_id>10</own_id>
<own_field>Sub1 field</own_field>
<own_id>11</own_id>
<own_field>Sub2 field</own_field>
</sub>
<unrelated>
<own_id>100</own_id>
<own_field>another field</own_field>
</unrelated>
</alldata>
</master>
and not
<master>
<alldata head_id="C6DF494E-4DA7-4726-8288-BB463CB96834">
<sub>
<own_id>10</own_id>
<own_field>Sub1 field</own_field>
</sub>
<sub>
<own_id>11</own_id>
<own_field>Sub2 field</own_field>
</sub>
<unrelated>
<own_id>100</own_id>
<own_field>another field</own_field>
</unrelated>
</alldata>
</master>
June 26, 2018 at 6:39 am
Maybe interesting to note that the 'for xml' statements are not allowed/supported inside union all queries
June 26, 2018 at 6:58 am
chrisongena - Tuesday, June 26, 2018 6:39 AMMaybe interesting to note that the 'for xml' statements are not allowed/supported inside union all queries
Do the UNION ALL in CTEs before the XML construct statement.
😎
June 26, 2018 at 7:25 am
Great Eirikur,
didn't think about that 😉
Now it works as I want.
Thank you
For those interested :declare @head1 table
(head_id uniqueidentifier)
declare @sub1 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @sub2 table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @unrelated table (
head_id uniqueidentifier,
own_id int,
own_field nvarchar(20))
declare @gd uniqueidentifier;
set @gd = newid();
insert into @head1 (head_id) values (@gd)
insert into @sub1 (head_id,own_id,own_field) values (@gd,10,'Sub1 field')
insert into @sub2 (head_id,own_id,own_field) values (@gd,11,'Sub2 field')
insert into @unrelated (head_id,own_id,own_field) values (@gd,100,'another field');
with subs (head_id,own_id,own_field)
as
( select sub1.head_id,sub1.own_id,sub1.own_field
from @sub1 sub1
union all
select sub2.head_id,sub2.own_id,sub2.own_field
from @sub2 sub2
)
select head.head_id
,( select * from subs where subs.head_id = head.head_id for xml path('sub'),type )
,(select unrelated.own_id,unrelated.own_field
from @unrelated unrelated where unrelated.head_id = head.head_id
for xml path('unrelated'),type )
from @head1 head
for xml auto,root('master')
June 26, 2018 at 7:33 am
You are welcome.
😎
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply