using sql to output xml - generating error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

using sql to output xml - generating error

Author

Topic

andy11983
Starting Member

3 Posts

Posted - 2006-07-19 : 11:37:23

Hi guys i'm a newbie to this forums so hopefully i've posted in the right place, so here goes .....

My company has been given a CMS to look at, thing is everything seems to be working except for this beasty stored procedure.. the purpose of this stored procedure is to gather data and output it as xml, but for some reason and i havent a clue why - i am getting an error stated below - i have also enclosed the stored procedured in hopes of some kind gururs can help me

Appreciate any and all help guys

Many thanks in advance

andy

ERRROR MESSAGE:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

[b]STORED PROCEDURE
/*
* spxCMGetTreeXML - Initiates the XML which is used to render the main content management tree.
*
* DATE DEVELOPER CHANGE
* 2004-03-11 DC Created
* 2004-03-22 CB Added the check for @blnIsGlobalAdmin before calling ftnCMCheckUserPermits
* 2004-03-24 DC Commented out above change. Now checking ftnCMCheckUserPermits at intance level as well.
* 2004-03-26 DC Implemented IsSingleInstance on CMObjects
* 2004-04-02 DC Implemented VersionNumber on CMVersions
* 2004-04-03 DC Only offer delete option for instances with no live children
* 2004-04-06 DC Now accepting and observing blnCompex
* 2004-04-29 DC Now accepting and observing blnForDialog
* 2005-02-22 AgMcC An instance with children can be deleted if all it's children have been deleted
* 2005-10-31 AgMcC Added friendly url (furl) so it can be used on internal links
* 2006.01.11 JS Added friendly url attribute for GoToLive
*/

CREATE PROCEDURE spxCMGetTreeXML (
@intBrandID INT=NULL,
@intAreaID INT=NULL,
@intUserID INT=NULL,
@blnComplex BIT,
@blnForDialog BIT=0
) AS

DECLARE @blnIsGlobalAdmin BIT

--last paramater overides 2nd last
IF @blnForDialog=1
BEGIN
SET @blnComplex=0
SET @blnIsGlobalAdmin = 0
END
ELSE
BEGIN
--work out of this user gets god-mode privs (1 = global admin role ID)
IF EXISTS ( SELECT * FROM SecurityUsers_SecurityRoles WHERE SecurityUsersID = @intUserID AND SecurityRolesID = 1 )
SET @blnIsGlobalAdmin = 1
END

--declare variables / tables to store intermediate data
DECLARE @objObjects TABLE ( [ID] INT,
[Description] NVARCHAR(128) COLLATE database_default,
DescriptionPlural NVARCHAR(128) COLLATE database_default,
CanAdd BIT,
CanEdit BIT,
CanDelete BIT,
CanOverwrite BIT,
CanView BIT,
HasTemplate BIT,
IsChild BIT,
HasKids BIT,
SingleInstance BIT,
InstanceCount INT NULL)
DECLARE @objInstances TABLE (InstanceID INT,
ObjectID INT,
[Name] NVARCHAR(128),
StateID INT,
WFStateID INT NULL,
MightHaveKids BIT,
ChildCount INT NULL)

--work out which types of object for the given brand/area this user has any rights to
INSERT INTO @objObjects ([ID], [Description], DescriptionPlural, CanAdd, CanEdit, CanDelete, CanOverwrite, CanView, HasTemplate, IsChild, HasKids, SingleInstance)
SELECT DISTINCT o.[ID],
o.[Description],
o.DescriptionPlural,
dbo.ftnCMCheckUserPermits(@intUserID, o.[ID], NULL, NULL, 1,0),
dbo.ftnCMCheckUserPermits(@intUserID, o.[ID], NULL,NULL, 2,0),
dbo.ftnCMCheckUserPermits(@intUserID, o.[ID], NULL, NULL, 3,0),
dbo.ftnCMCheckUserPermits(@intUserID, o.[ID], NULL,NULL, 4,0),
dbo.ftnCMCheckUserPermits(@intUserID, o.[ID], NULL,NULL, 6,0),
CASE WHEN cmot.[ID] IS NULL THEN 0 ELSE 1 END,
CASE WHEN cor.[ID] IS NULL THEN 0 ELSE 1 END,
CASE WHEN cor2.[ID] IS NULL THEN 0 ELSE 1 END,
o.IsSingleInstance
FROM CMObjects o
LEFT JOIN CMObjectRelationships cor
ON cor.TargetCMObjectID=o.[ID]
AND cor.CMRelationshipTypeID=1
LEFT JOIN CMObjectRelationships cor2
ON cor2.CMObjectID=o.[ID]
AND cor2.CMRelationshipTypeID=1
LEFT OUTER JOIN CMObjectTemplates cmot
ON cmot.CMObjectID = o.[ID]
WHERE (NavBrandsID=@intBrandID OR @intBrandID IS NULL)
AND (NavSiteAreasID = @intAreaID OR @intAreaID IS NULL)

--add all instances of objects which we have object-level permits to
INSERT INTO @objInstances(InstanceID, ObjectID, [Name], StateID, WFStateID, MightHaveKids)
SELECT i.[ID],
i.CMObjectID,
i.[Name],
i.CMStateID,
i.CMWorkflowStateID,
o.HasKids
FROM @objObjects o
JOIN CMInstances i
ON i.CMObjectID=o.[ID]
WHERE (o.CanEdit=1 OR o.CanDelete=1 OR o.CanOverwrite=1 OR o.CanView = 1)
AND (i.CMStateID<>5 OR @blnComplex=1) --no deleted instance in simple view, thanks
ORDER BY Name

--add instances of objects which we dont have object-level permits to, if we have intance-level permits
INSERT INTO @objInstances(InstanceID, ObjectID, [Name], StateID, WFStateID, MightHaveKids)
SELECT i.[ID], i.CMObjectID, i.[Name], i.CMStateID, i.CMWorkflowStateID, o.HasKids
FROM @objObjects o
JOIN CMInstances i
ON i.CMObjectID=o.[ID]
WHERE ((o.CanEdit=0 AND o.CanDelete=0 AND o.CanOverwrite=0 or o.CanView = 0)
AND dbo.ftnCMCheckUserPermits(@intUserID, NULL, i.[ID],NULL, NULL,0)=1)
AND (i.CMStateID<>5 OR @blnComplex=1) --no deleted instance in simple view, thanks
ORDER BY Name

--get child count for those instances that might have kids
UPDATE @objInstances SET ChildCount=(SELECT COUNT(*) FROM CMInstances WHERE CMParentInstanceID = InstanceID AND CMStateID <> 5) WHERE MightHaveKids=1

--get instance count for those object that might have instances
UPDATE @objObjects SET InstanceCount=(SELECT COUNT(*) FROM @objInstances WHERE ObjectID = [ID])

IF @blnComplex=1
-- generate 'content' tree for everyone
SELECT 1 AS tag, NULL AS parent,
'content' AS [node!1!id],
'Content' AS [node!1!description],
1 AS [node!1!isopen]
FOR XML EXPLICIT

IF EXISTS (SELECT * FROM @objObjects WHERE SingleInstance=1)
-- if there are any single-instance pages, a catch all top level item for them to be under
SELECT 1 AS tag, NULL AS parent,
'individual' AS [node!1!id],
'Individual pages' AS [node!1!description],
CASE @blnComplex WHEN 1 THEN 'content' ELSE NULL END AS [node!1!parentid],
0 AS [node!1!isopen]
FOR XML EXPLICIT

-- 1st OBJECT level data - where the object is not a child and you can add a new instance of it or there is already 1 or more instances
SELECT 1 AS tag, NULL AS parent,
'o' + CAST([ID] AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
DescriptionPlural AS [node!1!description],
CASE @blnComplex WHEN 1 THEN 'content' ELSE NULL END AS [node!1!parentid],
NULL AS [node!1!subtitle],
0 AS [node!1!isopen],
DescriptionPlural AS [node!1!sortorder],
HasTemplate AS [node!1!hastemplate],
1 AS [node!1!step],
NULL AS [command!2!id],
NULL AS [command!2!description]
FROM @objObjects
WHERE (IsChild=0 AND SingleInstance=0)
AND (CanAdd=1 OR CanEdit=1 OR CanDelete= 1 OR CanOverwrite = 1 OR CanView = 1 OR InstanceCount > 0)

-- ########### CHANGE TO UNION ALL ??????? FROM UNION#################
UNION ALL

-- 2nd generate content nodes (instances)
SELECT 1 AS tag, NULL AS parent,

'i' + CAST(i.[ID] AS VARCHAR) AS [node!1!id],
cmvfurl.FriendlyURL AS [node!1!friendlyurl],
i.[Name] AS [node!1!description],
CASE
WHEN i.CMParentInstanceID IS NULL AND ids.SingleInstance=0 THEN 'o' + CAST( i.CMObjectID AS VARCHAR) -- This object has more than one instance, therefore display the object
WHEN i.CMParentInstanceID IS NULL AND ids.SingleInstance=1 THEN 'individual' -- This is the only instance of the object - so set it as top level
WHEN i.CMParentInstanceID IS NOT NULL THEN 'i' + CAST(CMParentInstanceID AS VARCHAR) -- It's a child object, so nest under parent instance
END AS [node!1!parentid],
'(' + s.[Name] + COALESCE(' - ' + ws.[Name],'') + ')' AS [node!1!subtitle],
0 AS [node!1!isopen],
i.[Name] AS [node!1!sortorder],
HasTemplate AS [node!1!hastemplate],
2 AS [node!1!step],
NULL AS [command!2!id],
NULL AS [command!2!description]
FROM @objInstances oi
JOIN CMInstances i
ON oi.InstanceID = i.[ID]
JOIN CMStates s
ON i.CMStateID = s.[ID] --AND s.Render=1
LEFT JOIN CMWorkflowStates ws
ON i.CMWorkflowStateID = ws.[ID]
JOIN @objObjects ids
ON ids.[ID] = i.CMObjectID
INNER JOIN CMVersions cmv
ON i.[ID] = cmv.CMInstanceID
AND CMVersionTypeID = 2
LEFT OUTER JOIN CMVersionFriendlyURL cmvfurl
ON cmv.[ID] = cmvfurl.CMVersionID

UNION ALL

-- 4th generate 'add new...' command for top-level objects, where possible
SELECT 2 AS tag, 1 AS parent,
'o' + CAST(i.[ID] AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
4 AS [node!1!step],
'a' AS [command!2!id],
'Add New ' + [Description] + '...' AS [command!2!description]
FROM @objObjects i
WHERE i.CanAdd=1 AND i.IsChild=0 AND SingleInstance=0 AND @blnForDialog=0

UNION ALL

-- 4.5th generate 'permissions...' command for top-level objects, where possible
SELECT 2 AS tag, 1 AS parent,
'o' + CAST(i.[ID] AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
4.5 AS [node!1!step],
'p' AS [command!2!id],
'Permissions...' AS [command!2!description]
FROM @objObjects i
WHERE (IsChild=0 AND SingleInstance=0)
AND @blnIsGlobalAdmin=1 AND @blnForDialog=0 AND @blnComplex=0

UNION ALL

-- 5th generate 'add new...' command for instances, where possible. one for each instance of the object to which the chid object belongs

SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
5 AS [node!1!step],
'a' + CAST(o.[ID] AS VARCHAR) AS [command!2!id],
'Add New ' + o.[Description] + '...' AS [command!2!description]
FROM @objInstances i
JOIN @objObjects ids
ON ids.[ID] = i.ObjectID AND ids.HasKids=1
JOIN CMObjectRelationships cor
ON cor.CMObjectID=i.ObjectID AND cor.CMRelationshipTypeID=1
JOIN @objObjects o
ON cor.TargetCMObjectID = o.[ID] AND o.CanAdd=1
WHERE @blnForDialog=0

UNION ALL

-- 6th generate 'edit...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
6 AS [node!1!step],
'e' AS [command!2!id],
'Edit...' AS [command!2!description]
FROM @objObjects o
JOIN @objInstances i
ON o.[ID] = i.ObjectID
WHERE (o.CanEdit=1 OR dbo.ftnCMCheckUserPermits(@intUserID, NULL, i.InstanceID, NULL, 2,0)=1)
AND i.StateID=2 --(can only edit live instances)
AND @blnForDialog=0

UNION ALL

-- 7th generate 'delete...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
7 AS [node!1!step],
'd' AS [command!2!id],
'Delete...' AS [command!2!description]
FROM @objObjects o
JOIN @objInstances i
ON o.[ID] = i.ObjectID
WHERE (o.CanDelete=1 OR dbo.ftnCMCheckUserPermits(@intUserID, NULL, i.InstanceID,NULL, 3,0)=1)
AND i.StateID=2 AND (i.ChildCount=0 OR i.ChildCount IS NULL) --(can only delete live instances with no kids)
AND o.SingleInstance=0 --(cant delete single instance pages)
AND @blnForDialog=0

UNION ALL

-- 7.5th generate 'overwrite...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
7.5 AS [node!1!step],
'x' AS [command!2!id],
'Overwrite...' AS [command!2!description]
FROM @objObjects o
JOIN @objInstances i
ON o.[ID] = i.ObjectID
WHERE (o.CanOverwrite=1 OR dbo.ftnCMCheckUserPermits(@intUserID, NULL, i.InstanceID,NULL, 4,0)=1)
AND (i.StateID = 1 OR i.StateID=3) --(can only overwrite new or changed instances)
AND @blnForDialog=0

UNION ALL

-- 7.75th generate 'overwrite...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
7.75 AS [node!1!step],
'h' AS [command!2!id],
'Show History...' AS [command!2!description]
FROM @objInstances i
WHERE @blnForDialog=0

UNION ALL

-- 7.85th generate 'go to live page...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
7.85 AS [node!1!step],
'g' AS [command!2!id],
'Go to Live Page...' AS [command!2!description]
FROM @objInstances i JOIN CMStates s
ON i.StateID = s.[ID]
WHERE s.Render=1 AND @blnForDialog=0

UNION ALL

-- 8th generate 'permissions...' command for instances, where possible.
SELECT 2 AS tag, 1 AS parent,
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
NULL AS [node!1!friendlyurl],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
NULL AS [node!1!sortorder],
NULL AS [node!1!hastemplate],
7.5 AS [node!1!step],
'p' AS [command!2!id],
'Permissions...' AS [command!2!description]
FROM @objInstances i
WHERE @blnIsGlobalAdmin=1 AND @blnForDialog=0

ORDER BY [node!1!id], parent, tag, [node!1!parentid]

FOR XML EXPLICIT

IF @blnComplex=1
BEGIN

-- generate content "version" parent nodes
SELECT DISTINCT 1 AS tag, NULL AS parent,
'iv' + CAST(i.InstanceID AS VARCHAR) AS [node!1!id],
'Versions' AS [node!1!description],
'i' + CAST(i.InstanceID AS VARCHAR) AS [node!1!parentid],
NULL AS [node!1!subtitle],
1 AS [node!1!isopen],
2.5 AS [node!1!step],
NULL AS [command!2!id],
NULL AS [command!2!description]
FROM CMVersions v
JOIN @objInstances i
ON v.CMInstanceID = i.InstanceID
FOR XML EXPLICIT

-- generate version nodes
SELECT 1 AS tag, NULL AS parent,
'v' + CAST(v.[ID] AS VARCHAR) AS [node!1!id],
CAST(v.VersionNumber AS VARCHAR) + '. ' + vt.[Name] AS [node!1!description],
'iv' + CAST(i.InstanceID AS VARCHAR) AS [node!1!parentid],
'(' + dbo.ftnInfraFormatDateTime(v.LastEditDate) + ' - ' + su.FirstName + ' ' + su.LastName + ')' AS [node!1!subtitle],
0 AS [node!1!isopen],
v.[ID] AS [node!1!sortme],
NULL AS [command!2!id],
NULL AS [command!2!description]
FROM CMVersions v
JOIN SecurityUsers su
ON v.AuthorUserID = su.[ID]
JOIN CMVersionTypes vt
ON v.CMVersionTypeID = vt.[ID]
JOIN @objInstances i
ON v.CMInstanceID = i.InstanceID

UNION

-- generate "preview" command for version nodes
SELECT 2 AS tag, 1 AS parent,
'v' + CAST(v.[ID] AS VARCHAR) AS [node!1!id],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
v.[ID] AS [node!1!sortme],
'wp' AS [command!2!id],
'Show Page...' AS [command!2!description]
FROM CMVersions v
JOIN @objInstances i
ON v.CMInstanceID = i.InstanceID

UNION

-- generate "show data" command for version nodes
SELECT 2 AS tag, 1 AS parent,
'v' + CAST(v.[ID] AS VARCHAR) AS [node!1!id],
NULL AS [node!1!description],
NULL AS [node!1!parentid],
NULL AS [node!1!subtitle],
NULL AS [node!1!isopen],
v.[ID] AS [node!1!sortme],
'wd' AS [command!2!id],
'Show Data...' AS [command!2!description]
FROM CMVersions v
JOIN @objInstances i

ON v.CMInstanceID = i.InstanceID

ORDER BY [node!1!sortme], tag ASC
FOR XML EXPLICIT

END

IF @blnIsGlobalAdmin=1 AND @blnComplex=1
BEGIN

-- generate 'permissions...' tree for global admins only.
SELECT 1 AS tag, NULL AS parent,
'permits' AS [node!1!id],
'Object Permissions' AS [node!1!description],
0 AS [node!1!isopen]
FOR XML EXPLICIT

SELECT 1 AS tag, NULL AS parent,
'p' + CAST([ID] AS VARCHAR) AS [node!1!id],
DescriptionPlural AS [node!1!description],
'permits' AS [node!1!parentid],
NULL AS [node!1!subtitle],
1 AS [node!1!isopen],
DescriptionPlural AS [node!1!sortorder]
FROM @objObjects
FOR XML EXPLICIT

END
GOb]

andy11983
Starting Member

3 Posts

Posted - 2006-07-20 : 04:16:36

hi again,

i have figured out where the error message is coming from but cant understand why.

the error is generated by that line,

ORDER BY [node!1!id], parent, tag, [node!1!parentid]

can anybody please help

cheers

andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources