FOR XML EXPLICIT
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
UNION ALL
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
)
SELECT
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
Sample-Data.xlsx
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
UNION ALL
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
)
SELECT
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
Sample-Data.xlsx
ASKER
Hi Harish
I think I am making a mess of it! But really can you explain a little more about the ROW_NUMBERS issue. From what I had read to this point I took it that tag was a contiguous count determining order in the XML. From what you have said here it is not. I almost believe it corresponds to a single itteration of the XML's schema. Is this what you are saying? And please in this case assume I know nothing.
/Aly
I think I am making a mess of it! But really can you explain a little more about the ROW_NUMBERS issue. From what I had read to this point I took it that tag was a contiguous count determining order in the XML. From what you have said here it is not. I almost believe it corresponds to a single itteration of the XML's schema. Is this what you are saying? And please in this case assume I know nothing.
/Aly
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think Harish has given me some sound advice here
When you use FOR XML EXPLICIT, you are defining the levels and the elements explicitly in your SELECT statement. For example, when you give an alieas "Department!3!Dep!Element"
-Harish