I am having some problems with the SQL below. This is the first time I have worked with the explicit version and the errors I am getting are unfamiliar.
The error I have is
Msg 6806, Level 16, State 2, Line 1
Undeclared tag ID 4 is used in a FOR XML EXPLICIT query.
Can someone perhaps point me in the right direction please. I have attached a data set that matches the output of the CTE element of the query, this hopefully will give further assistance to the expert.
WITH Divi AS
SELECT NULL AS 'Parent', di.DivisionID, di.DivisionNo,NULL AS 'DeptId', di.DivisionName, Null AS 'DepartmentNumber', CAST(NULL AS Varchar(50)) AS 'DepartmentName' from Division di
SELECT dp.DivisionID AS 'Parent',NULL 'DivisionID' , NULL AS 'DivisionNo', dp.DeptId, Null AS 'DivisionName', dp.DepartmentNumber, dp.DepartmentName FROM Divi div JOIN Department dp ON div.DivisionID = dp.DivisionID
CAST(ROW_NUMBER() OVER(ORDER BY ISNULL(di.Parent, di.DivisionID),di.DeptId) AS INT) AS 'Tag',
ISNULL(di.Parent,0) AS 'Parent',
DivisionID AS [DivisionId!1!Div],
DivisionNo AS [DivisionNo!2!Number],
DivisionName AS [DivisionNo!2!Number],
DeptId AS [Department!3!Dep!Element]
DepartmentNumber AS [Department!3!Number],
DepartmentName AS [Department!3!Name]
FROM Divi di FOR XML EXPLICIT