January 28, 2008 at 2:01 pm
Hi Jacob and everyone in the forum,
I was looking for an online tutorial on FOR XML EXPLICIT and came across this brilliant site.
I would like to thank you Jacob for putting together this wonderful tutorial about FOR XML in SQL 2000/2005.
I am struggling to get the FOR XML EXPLICIT to work for me and would appreciate some help.
The FOR XML EXPLICIT code below is long but it should not be so complicated to understand. The problem happens when I try to add Tag 6 as a subelement of Tag 3 like this:
SELECT DISTINCT 6 AS Tag,
3 AS Parent,
-----
This is the error message I get:
'Msg 6833, Level 16, State 1, Line 2
Parent tag ID 4 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.'
I have added [StandardReports!4!element] to the ORDER BY clause but
to no avail. I am trying to add Tag 6 [CustomReports!6!element] as a subelement of Tag 3 [DataTemplate!3!key].
Please note that the query works fine without the FOR XML EXPLICIT
Could someone please shed some light here?
Cheers
CJ
SELECT DISTINCT 1 AS Tag,
NULL AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
NULL AS [DataTemplate!3!key],
NULL AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
UNION ALL
SELECT DISTINCT 2 AS Tag,
1 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
NULL AS [DataTemplate!3!key],
NULL AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
UNION ALL
SELECT DISTINCT 3 AS Tag,
2 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
UNION ALL
SELECT DISTINCT 4 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (40, 1) or (rls.UserId is null)
UNION ALL
SELECT DISTINCT 5 AS Tag,
4 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
rl.ReportLayoutId AS [StandardReport!5!id],
rl.ReportOrder AS [StandardReport!5!order!hide],
rls.ReportLayoutName AS [StandardReport!5!name],
rls.[Description] AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (40, 1) or (rls.UserId is null)
UNION ALL
SELECT DISTINCT 6 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
rl.ReportLayoutId AS [StandardReport!5!id],
rl.ReportOrder AS [StandardReport!5!order!hide],
rls.ReportLayoutName AS [StandardReport!5!name],
rls.[Description] AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
FROM tblModule m LEFT JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
LEFT JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (40, 1) or (rls.UserId is null)
ORDER BY [Module!1!order!hide], [DataTemplates!2!element], [DataTemplate!3!order!hide], [StandardReports!4!element], [StandardReport!5!order!hide], [CustomReports!6!element]
FOR XML EXPLICIT
========================
XML I NEED TO PRODUCE
-----
-----
January 28, 2008 at 4:11 pm
I'm not sure why you haven't seen this error yet, since there seems to be a logic flaw in how you're building level 4.
To highlight it:
SELECT DISTINCT 4 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
in short - there needs to be a way for 4 and five to "fall into play" and because it's completely null, either the linking isn't happening, or the nulls at this level are causing it to order the "4" level record AFTER the level 5 stuff (which is going to cause your error).
If you take the FOR XML EXPLICIT off - you should see the 5's are probably immediately after 3's and not AFTER the 4 record.
Try adding any silly field, put a value into it at this level, and include that field in your ORDER by, even if it's a constant - just make is so that the ordering puts them in
1
2
3
4
5
6
order and not
1
2
3
5
4
6
ordering....
Does that make sense?
Also - for what it's worth - your XML got "munched" by the post editor. Before posting that kind of stuff, run a find/replace and replace the less than and greater than with their HTML codes, so that it will post
by the way - for < , it's < and for > it's >
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 5:10 pm
Hi SSCrazy,
Thanks for your reply.
Please bear in mind that 'FOR XML EXPLICIT' is new to me and I have had quite a task to be able to get where I am. Everything was working fine up to Tag 5 and then when I added Tag 6 I got the dreaded error.
Maybe the reason why I did not spot the error is because I have done the same thing before for level 2 and did not get any error:
LEVE 2
Here I added an Element called DataTemplates to the Module element like this:
SELECT DISTINCT 2 AS Tag,
1 AS Parent,
NULL AS [DataTemplates!2!element],
However I never assigned any value to it and it worked. Therefore, I thought I could do the same for the StandardReports element.
Any more ideas of what could be wrong?
Cheer
CJ
______________________________
I have tried to add an empty string to it as you suggested
'' AS [StandardReports!4!element],
but I still got the same error. I am not sure if this will work since this element can only have child elements 'StandardReport. So, I have to figure out a way to create the parent element StandardReports without any value.
SELECT DISTINCT 4 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
'' AS [StandardReports!4!element],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [CustomReports!6!element]
Running the code without FOR XML EXPLICIT produces these Tags and Parent
TAG PARENT
1NULL
21
32
43
54
63
1NULL
21
32
43
54
63
54
63
32
32
43
54
63
32
43
54
63
1NULL
21
32
43
54
63
32
43
54
63
32
43
54
63
54
63
32
43
54
63
32
43
54
63
1NULL
21
32
43
54
63
32
43
54
63
1NULL
21
32
43
54
63
1NULL
21
32
43
54
63
32
43
54
63
1NULL
21
32
43
54
63
32
43
54
63
32
43
54
63
1NULL
21
32
43
54
63
================================
This is the XML I am trying to produce escaped as you suggested:
<Modules culture="" >
<Module key="project" name="Project" desc="" >
<Workflow >
<tree label="Project" key="proj" >
<item label="Current Project" > </item >
<item label="Recent Projects" > </item >
</tree >
</Workflow >
<DataTemplates >
<DataTemplate key="test" name="test" desc="test" >
<DataTypes >
<Analysis name="NetQuestAdex" >
<Measures >
<Measure name="Cost" / >
</Measures >
</Analysis >
</DataTypes >
<StandardReports >
<StandardReport id="57" name="Detail By Month - Pages" desc="" >
<ReportSpec >
<Cols >
<ColDim name="Time" level="Month" / >
</Cols >
</ReportSpec >
</StandardReport >
</StandardReports >
<CustomReports >
<CustomReport id="302" name="Custom Efficiency Report" desc="" >
<ReportSpec >
-----
</ReportSpec >
</CustomReport >
</CustomReports >
</DataTemplate >
</DataTemplates >
</Module >
</Modules >
January 28, 2008 at 6:54 pm
it's never quite what you expect...for XML explicit is tricky, and particularly due to the ordering.
you're right - it did end up creating the reports level (I didn't think it would with the syntax.) But - thanks to you showing what you got, the error appears anyway :).
Your issue now has a bit to do with ordering or orphans it seems, and it could be one of two things. A sample first (where one example of the problem is):
TAG PARENT
1 NULL
2 1
3 2
4 3
5 4
6 3
1 NULL
2 1
3 2
4 3
5 4
6 3
5 4
6 3
The problem area is bolded. Now - one of two things is going on. Either the "3" and "4" level missing for that set, or the ordering is incorrect. It chokes on that because the "first" 6 3 "hides" the 4 3 that's up a little, so it knows the SECOND 5 4 can't be its child. So - it throws the "I'm an orphan" error.
Meaning - it should look like
1 NULL
2 1
3 2
4 3
5 4
6 3
3 2 --<-- this was inserted by me to "fix" the issue
4 3 --<-- this was inserted by me to "fix" the issue
5 4
6 3
or
1 NULL
2 1
3 2
4 3
5 4
5 4
6 3
6 3
In other words you have a "Orphan level 5" looking for its parent level "4".
Of course - you have several of these going on and it's only going to see the first error.
In general - you need to explicitly make sure that all of the children levels have "parents" or you will see this error again. That's one of the (IMO) weaknesses of this syntax - it's VERY picky (and for good reason): I just wish the errors were a little clearer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 1, 2008 at 12:28 pm
Hi Matt,
Thanks a lot for your help and input.
After many trials and errors I managed to come up with a version of the XML Explicit that it seems to be working.
There only two problems to sort out:
1 - For some reason I lost the ability to sort the results using my hide directive. These were working before but they no longer work:
m.ModuleOrder AS [Module!1!order!hide]
dt.TemplateOrder AS [DataTemplate!3!order!hide]
rl.ReportOrder AS [StandardReport!5!order!hide]
2 - I can not add any of the items in TAG 5 to the ORDER BY clause. If I do so, I get the dreaded TAG 4 has not been opened error. So That is how my ORDER BY look at the moment:
ORDER BY [Module!1!key], [Module!1!order!hide], [DataTemplates!2!!xml], [DataTemplate!3!key], [DataTemplate!3!order!hide], [StandardReports!4!!xml], [CustomReports!6!!xml], [CustomReport!7!id]
Do you have any ideas why the [Module!1!order!hide], [DataTemplate!3!order!hide] are not sorting the result. Also, why can't I add the TAG 5 - rl.ReportOrder AS [StandardReport!5!order!hide] - to the order by clause?
Cheers
CJ
========================
The code
SELECT DISTINCT 1 AS Tag,
NULL AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
ms.ModuleName AS [Module!1!name],
ms.[Description] AS [Module!1!desc],
CONVERT(NVARCHAR(4000), ms.WorkflowXML) AS [Module!1!Workflow!xml],
NULL AS [DataTemplates!2!!xml],
NULL AS [DataTemplate!3!key],
NULL AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!!xml],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
NULL AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
UNION ALL
SELECT DISTINCT 2 AS Tag,
1 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!!xml],
NULL AS [DataTemplate!3!key],
NULL AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!!xml],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
NULL AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
UNION ALL
SELECT DISTINCT 3 AS Tag,
2 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!!xml],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
dts.TemplateName AS [DataTemplate!3!name],
dts.[Description] AS [DataTemplate!3!desc],
CONVERT(NVARCHAR(4000), dts.DataTypesXML) AS [DataTemplate!3!DataTypes!xml],
NULL AS [StandardReports!4!!xml],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
NULL AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
UNION ALL
SELECT DISTINCT 4 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
'' AS [StandardReports!4!!xml],
NULL AS [StandardReport!5!id],
NULL AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
NULL AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (33, 1) or (rls.UserId is null)
UNION ALL
SELECT DISTINCT 5 AS Tag,
4 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
'' AS [StandardReports!4!!xml],
rl.ReportLayoutId AS [StandardReport!5!id],
rl.ReportOrder AS [StandardReport!5!order!hide],
rls.ReportLayoutName AS [StandardReport!5!name],
rls.[Description] AS [StandardReport!5!desc],
CONVERT(NVARCHAR(4000), rls.ReportLayoutXML) AS [StandardReport!5!!xml],
NULL AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (33, 1) or (rls.UserId is null)
UNION ALL
SELECT DISTINCT 6 AS Tag,
3 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
'' AS [StandardReports!4!!xml],
rl.ReportLayoutId AS [StandardReport!5!id],
rl.ReportOrder AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
'' AS [CustomReports!6!!xml],
NULL AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (33, 1) or (rls.UserId is null)
UNION ALL
SELECT DISTINCT 7 AS Tag,
6 AS Parent,
m.ModuleKey AS [Module!1!key],
m.ModuleOrder AS [Module!1!order!hide],
NULL AS [Module!1!name],
NULL AS [Module!1!desc],
NULL AS [Module!1!Workflow!xml],
'' AS [DataTemplates!2!element],
dt.TemplateKey AS [DataTemplate!3!key],
dt.TemplateOrder AS [DataTemplate!3!order!hide],
NULL AS [DataTemplate!3!name],
NULL AS [DataTemplate!3!desc],
NULL AS [DataTemplate!3!DataTypes!xml],
'' AS [StandardReports!4!!xml],
rl.ReportLayoutId AS [StandardReport!5!id],
rl.ReportOrder AS [StandardReport!5!order!hide],
NULL AS [StandardReport!5!name],
NULL AS [StandardReport!5!desc],
NULL AS [StandardReport!5!!xml],
'' AS [CustomReports!6!!xml],
'TEST' AS [CustomReport!7!id]
FROM tblModule m inner JOIN tblModuleString ms ON m.ModuleKey = ms.ModuleKey
inner JOIN tblCulture c on ms.CultureId = c.CultureId
LEFT JOIN tblDataTemplate dt on m.ModuleKey = dt.ModuleKey
LEFT JOIN tblDataTemplateString dts on dt.TemplateKey = dts.TemplateKey
LEFT join tblReportLayout rl on dt.TemplateKey = rl.TemplateKey
LEFT join tblReportLayoutString rls on rls.ReportLayoutId = rl.ReportLayoutId
WHERE rls.UserId in (33, 1) or (rls.UserId is null)
ORDER BY [Module!1!key], [Module!1!order!hide], [DataTemplates!2!!xml], [DataTemplate!3!key], [DataTemplate!3!order!hide], [StandardReports!4!!xml], [CustomReports!6!!xml], [CustomReport!7!id]
FOR XML EXPLICIT
=======================
TAGS
TAG PARENT
1NULL
21
32
43
54
63
76
32
43
54
54
63
63
76
76
32
43
54
54
63
63
76
76
32
43
54
63
76
32
43
54
63
76
1NULL
21
32
43
54
63
76
32
43
54
63
76
32
43
54
63
76
1NULL
21
32
43
54
63
76
32
43
54
63
76
32
43
54
54
63
63
76
76
32
1NULL
21
32
43
54
63
76
1NULL
21
32
43
54
63
76
1NULL
21
32
43
54
63
76
32
43
54
63
76
1NULL
21
32
43
54
63
76
32
43
54
63
76
1NULL
21
32
43
54
63
76
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply