Solved

FOR XML EXPLICIT

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 41
SQL Query 2 34
SQL Log size 3 20
SQL - Rewrite statement 4 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

821 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