Solved

FOR XML EXPLICIT

Posted on 2014-02-24
4
773 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
 

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 500 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
 

Author Closing Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now