organize xml query output

Question

Post reply

organize xml query output

fabriziodb

Mr or Mrs. 500

Points: 562
More actions
January 19, 2016 at 4:49 am

#323647

USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.RelKeysDocs') IS NOT NULL DROP TABLE dbo.RelKeysDocs;
IF OBJECT_ID(N'dbo.Keys') IS NOT NULL DROP TABLE dbo.Keys;
IF OBJECT_ID(N'dbo.Tags') IS NOT NULL DROP TABLE dbo.Tags;
CREATE TABLE dbo.RelKeysDocs
(
IdKeyINT NOT NULL,
IdDocINT NOT NULL
);
CREATE TABLE dbo.Keys
(
IdKey INT NOT NULL
,Name VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Tags
(
IdDoc INT NOT NULL
,Tag VARCHAR(10) NOT NULL
);
INSERT INTO dbo.RelKeysDocs(IdKey, IdDoc)
VALUES (1,1),(2,2),(3,1),(3,3)
INSERT INTO dbo.Keys( IdKey,Name)
VALUES (1,'K1'),(2,'K2'),(3,'K3');
INSERT INTO dbo.Tags(IdDoc, Tag)
VALUES (1,'T1'),(2,'T2'),(1,'T3');
Select RKD.IdDoc, K.Name, T.Tag From RelKeysDocs RKD
Left Join Keys K on RKD.IdKey = K.IdKey
Left Join Tags T on RKD.IdDoc = T.IdDoc
for xml auto
this is the output:
<RKD IdDoc="1">
<K Name="K1">
<T Tag="T1" />
<T Tag="T3" />
</K>
</RKD>
<RKD IdDoc="2">
<K Name="K2">
<T Tag="T2" />
</K>
</RKD>
<RKD IdDoc="1">
<K Name="K3">
<T Tag="T1" />
<T Tag="T3" />
</K>
</RKD>
<RKD IdDoc="3">
<K Name="K3">
<T />
</K>
</RKD>
i would like to group by IdDoc and all nodes to be children of RKD
<RKD IdDoc="1">
<K Name="K1" />
<K Name="K3">
<T Tag="T1" />
<T Tag="T3" />
</RKD>
<RKD IdDoc="2">
<K Name="K2" />
<T Tag="T2" />
</RKD>
<RKD IdDoc="3">
<K Name="K3" />
<T />
</RKD>
Thanks.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

You are probably best off using FOR XML PATH for this in my opinion.

Select

-- Make IdDoc an Attribute by prefixing with @

R.IdDoc [@IdDoc]

-- Get all the Keys for this IdDoc as an XML subset

, (

select K.Name [@Name]

from RelKeysDocs RK

join dbo.Keys K on RK.IdKey = K.IdKey

where RK.IdDoc = R.IdDoc

for xml path('K'), type

)

-- Get all the Tags for this IdDoc as an XML subset

, (

select T.Tag [@Tag]

from Tags T

where R.IdDoc = T.IdDoc

for xml path('T'),type

)

From (

-- Grab all the IdDoc numbers we are interested in

Select distinct RKD.IdDoc

from RelKeysDocs RKD

) R

order by R.IdDoc

-- Ensure the base node for each IdDoc is "RKD"

for xml path('RKD');

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);