?
Solved

FOR XML EXPLICIT

Posted on 2014-02-24
4
Medium Priority
?
872 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 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