Solved

FOR XML EXPLICIT

Posted on 2014-02-24
4
836 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
 
LVL 1

Author Closing Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Retrieve a Registry value using SCCM 2012. 2 45
Search Text in Views 2 27
VM SQL server license. 1 62
sql server string_split 4 25
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

739 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