?
Solved

FOR XML EXPLICIT

Posted on 2014-02-24
4
Medium Priority
?
946 Views
Last Modified: 2014-02-25
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
0
Comment
Question by:Alyanto
  • 2
  • 2
4 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39882201
Hello,

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", you say that "Department" is a group identified by tag 3 and "Dep" is an element under Department. And as you can see, you have defined tags only upto 3. But, you are using a serial number (ROW_NUMBER()) for TAG. And any tag above 3 is not allowed since you have defined tags only upto level 3. Please explain what do you really want to achive.

-Harish
0
 
LVL 1

Author Comment

by:Alyanto
ID: 39882635
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
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 2000 total points
ID: 39883077
Hi Aly,

Tag and Parent are the columns that forms the hierarchy in XML results. It is better that you learn more about XML EXPLICIT before doing any trial and error. Here is an overall idea about XML EXPLICIT:
http://technet.microsoft.com/en-us/library/ms189068.aspx

And here is a detailed tutorial if you are interested:
FOR XML EXPLICIT Tutorial

-Harish
0
 
LVL 1

Author Closing Comment

by:Alyanto
ID: 39885145
I think Harish has given me some sound advice here
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question