1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88

create procedure [dbo].[GetXML] (
	@ExternalId		varchar(255)
)	
as
begin
	declare @Contained int = 2;
	declare @Sent int = 3;
	declare @ResultAdmissionSubmissionNO int = 76;
	declare @dcStateSent int = 7;
	declare @dcStateInProgress int = 1;
	declare @dcStateFinishedWithError int = 2;
	declare @dcStateFinished int = 3;

with ExternalIdMain as (
select 
	ExternalId as ExternalId,
	dc.DocCircId as GroupId,
	dc.DocCircId as DocCircId
	from 
	[dbo].[gate_DocCirculations] as gdc
	inner join [dbo].[DocCirculations] as dc on dc.DocCircId = gdc.DocCircId and not exists 
		(select TOP 1 1 from [dbo].[DocCircConnection] as dcci where dcci.Parent = dc.DocCircId or dcci.Child = dc.DocCircId )
	where ExternalId = @ExternalId and 
		dc.StateId in (@dcStateSent, @dcStateInProgress, @dcStateFinishedWithError, @dcStateFinished)
	union all
select 
	ExternalId as ExternalId,
	dcc0.Parent as GroupId,
	dcc0.Child as DocCircId
	from 
	--dcc0.Child is leaf
	[dbo].[DocCircConnection] as dcc0 
	inner join [dbo].[DocCircConnection] as dcc1 on dcc0.Parent = dcc1.Child and dcc0.ConnectionType = @Contained and dcc1.ConnectionType = @Sent
	inner join [dbo].[gate_DocCirculations] as gdc on dcc1.Parent = gdc.DocCircId
	where ExternalId = @ExternalId
),
ExternalIdStates as (
	select 
		eim.ExternalId as ExternalId,
		eim.GroupId as GroupId, 
		dcsg.StateId as GroupState, 
		dcsg.[Description] as GroupStateDescription, 
		eim.DocCircId as DocCircId, 
		dcs.StateId as DocCircState, 
		dcs.[Description] as DocCircStateDescription, 
		dc.timeUpdate as [Date] 
		from 
	ExternalIdMain as eim
	inner join dbo.DocCirculations as dc on eim.DocCircId = dc.DocCircId
	inner join dbo.DocCirculations as dcg on eim.GroupId = dcg.DocCircId
	inner join dbo.DocCircStates as dcs on dcs.StateId = dc.StateId
	inner join dbo.DocCircStates as dcsg on dcsg.StateId = dcg.StateId
),
ExternalIdFull as (
select
	ExternalId as ExternalId,
	GroupId as GroupId,
	GroupState as GroupState,
	GroupStateDescription as GroupStateDescription,
	eis.DocCircId as DocCircId,
	DocCircState as DocCircState,
	DocCircStateDescription as DocCircStateDescription,
	[Date] as [Date],
	[Filename] as [Filename]
from 
ExternalIdStates as eis
left join dbo.Transactions as t on eis.DocCircId = t.DocCircId and t.idTranstype = @ResultAdmissionSubmissionNO
left join dbo.Docs as d on t.idTransaction = d.idTransaction
left join dbo.Contents as c on d.idContent = c.idContent
)
select ExternalId as "@Value",
(
	select GroupId as "@GroupId", Max(GroupState) as "@GroupState", Max(GroupStateDescription) as "@GroupStateDescription",
	(
		select DocCircId as "@DocCircId", Max(DocCircState) as "@DocCircState", Max(DocCircStateDescription) as "@DocCircStateDescription", Max([Date]) as "@Date",
		(
			--"case when" for null filenames
			case when 
						(exists (select TOP 1 [Filename] from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId and [Filename] is not null))
				then (select [Filename] as "@Value" from ExternalIdFull as eif3 where eif3.DocCircId = eif2.DocCircId FOR XML PATH('Filename'), type) 
				else N'' end
		)
		from ExternalIdFull as eif2 where eif2.GroupId = eif1.GroupId group by DocCircId FOR XML PATH('DocCirc'), type
	)
	from ExternalIdFull as eif1 where eif1.ExternalId = eif0.ExternalId group by GroupId FOR XML PATH('Group'), type
)
from ExternalIdFull as eif0 group by ExternalId FOR XML PATH('ExternalId'), Root('Root')
end