FOR XML EXPLICIT syntax error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

FOR XML EXPLICIT syntax error

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2003-05-29 : 12:05:06

--Does anybody have an explanation why the regular select
--with a dynamic order by clause works
-- and the one with FOR XML EXPLICIT does not?

use northwind
declare @sort tinyint
set @sort=0
select o.orderid, companyname,
convert(varchar(10), orderdate, 101) as 'dateordered',
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by case
when @sort = 0 then companyname
when @sort=1 then productname
when @sort=2 then 'dateordered'
when @sort=3 THEN replicate(' ',20-len(cast((d.Unitprice*quantity) as varchar(20))))+cast((d.Unitprice*quantity) as varchar(20)) end
go
-- and the one with FOR XML EXPLICIT does not?
declare @sort tinyint
set @sort=0
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [orders!2!OrderID],
NULL AS [orders!2!CompanyName],
NULL AS [orders!2!orderdate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
NULL, NULL
from orders o join customers c on o.customerid = c.customerid
union all
SELECT 3,
2,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by case
when @sort = 0 then [orders!2!CompanyName]
when @sort=1 then [orderDet!3!ProductName]
when @sort=2 then [orders!2!orderdate]
when @sort=3 THEN [orderDet!3!Amount]
END FOR XML EXPLICIT
go
-- but fixed order by will

declare @sort tinyint
set @sort=0
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [orders!2!OrderID],
NULL AS [orders!2!CompanyName],
NULL AS [orders!2!orderdate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
NULL, NULL
from orders o join customers c on o.customerid = c.customerid
union all
SELECT 3,
2,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
d.Unitprice*quantity as 'Amount',
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
order by [orders!2!CompanyName]
-- [orderDet!3!ProductName]
--[orders!2!orderdate]
-- [orderDet!3!Amount]
FOR XML EXPLICIT

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-29 : 16:06:08

It doesn't have anything to do with FOR XML Explicit you get the same error either way:


DECLARE @sort tinyint 
SET @sort=0 
   SELECT 1 AS Tag 
	, NULL AS Parent 
	, NULL AS [ROOT!1] 
	, NULL AS [orders!2!OrderID] 
	, NULL AS [orders!2!CompanyName] 
	, NULL AS [orders!2!orderdate] 
	, NULL AS [orderDet!3!Amount] 
	, NULL AS [orderDet!3!ProductName] 
UNION ALL 
   SELECT 2
	, 1
	, NULL
	, o.orderid
	, companyname
	, convert(varchar(10), orderdate, 101)
	, NULL
	, NULL 
     FROM Orders o 
     JOIN customers c ON o.customerid = c.customerid 
UNION ALL 
   SELECT 3 
	, 2 
	, NULL 
	, o.orderid 
	, companyname 
	, convert(varchar(10), orderdate, 101) 
	, d.Unitprice*quantity
	, productname 
     FROM orders o 
     JOIN [order details] d ON o.orderid = d.orderid 
     JOIN customers c 	    ON o.customerid = c.customerid 
     JOIN products p 	    ON d.productid = p.productid 
 ORDER BY CASE 
		WHEN @sort=0 THEN [orders!2!CompanyName] 
		WHEN @sort=1 THEN [orderDet!3!ProductName] 
		WHEN @sort=2 THEN [orders!2!orderdate] 
		WHEN @sort=3 THEN [orderDet!3!Amount]
			     ELSE [orders!2!OrderID] 
	  END

I'll try and figure out whats up.

BTW, whats with the MS ACCESS FORM Looking column names. Never have seen that style before. Didn't think it would be valid but it seems to be. Is there any special meaning to have a column referenced like that?

Brett

8-)

Edited by - x002548 on 05/29/2003 16:06:47

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 09:34:32

Still on the hunt...

could be a quirky MS thingee...

Try this..the error message doesn't make sense..it's pretty simple:


USE Northwind
GO
DECLARE @x int
SELECT @x = 1
SELECT	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM 	  Orders
UNION ALL
SELECT	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM 	  Orders
ORDER BY CASE   WHEN @x=1 THEN OrderId
		WHEN @x=2 THEN CustomerId
	 END

Returns:

Server: Msg 104, Level 15, State 1, Line 3
ORDER BY items must appear in the select list if the statement contains a UNION operator.

Which obviously they do....

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-30 : 09:42:00

The exclamations in the column names are an EXPLICIT thing. See "Using EXPLICIT Mode" in BOL.

Almost anything can be column name if you quote it!
CREATE TABLE "4" ("2" int, "5" int, "6" int, "7" int)
SELECT "2" AS "3" FROM "4" WHERE "5" = "6" ORDER BY "7"

The error you get with this cracks me up:
CREATE TABLE #Test ("" int)

Edited by - Arnold Fribble on 05/30/2003 09:51:03

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-30 : 09:47:42

Sure the error makes sense. That case expression isn't one of columns being selected.
Do something like this:


SELECT *
FROM (
SELECT	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM 	  Orders
UNION ALL
SELECT	  OrderID
	, CustomerID
	, EmployeeID
	, OrderDate
	, RequiredDate
	, ShippedDate
	, ShipVia
	, Freight
	, ShipName
	, ShipAddress
	, ShipCity
	, ShipRegion
	, ShipPostalCode
	, ShipCountry
  FROM 	  Orders
) AS A
ORDER BY CASE   WHEN @x=1 THEN OrderId
		WHEN @x=2 THEN CustomerId

Edited by - Arnold Fribble on 05/30/2003 09:48:15

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 10:27:14

THANKS Arnold...but it doesn't make ANY sense to me...The first part of the union should be defining the names of the columns. It appears that if you make a derived table, then all is well in SQL Server land...makes no sense. But the following does work....we just now have a different problem when we add FOR XML EXPLICIT... I get:

Server: Msg 6802, Level 16, State 1, Line 3
FOR XML EXPLICIT query contains the invalid column name 'ROOT'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.

But this will run now:


DECLARE @sort tinyint 
SET @sort=0
SELECT * FROM (
   SELECT 1 AS Tag 
	, 0 AS Parent 
	, NULL AS ROOT 
	, NULL AS OrderID 
	, NULL AS CompanyName 
	, NULL AS orderdate 
	, NULL AS Amount 
	, NULL AS ProductName 
UNION ALL 
   SELECT 2
	, 1
	, NULL
	, o.orderid
	, companyname
	, convert(varchar(10), orderdate, 101)
	, NULL
	, NULL 
     FROM Orders o 
     JOIN customers c ON o.customerid = c.customerid 
UNION ALL 
   SELECT 3 
	, 2 
	, NULL 
	, o.orderid 
	, companyname 
	, convert(varchar(10), orderdate, 101) 
	, d.Unitprice*quantity
	, productname 
     FROM orders o 
     JOIN [order details] d ON o.orderid = d.orderid 
     JOIN customers c 	    ON o.customerid = c.customerid 
     JOIN products p 	    ON d.productid = p.productid 
) AS xxx
 ORDER BY CASE @sort
		WHEN 0 THEN CONVERT(varbinary,CompanyName) 
		WHEN 1 THEN CONVERT(varbinary,ProductName)
		WHEN 2 THEN CONVERT(varbinary,orderdate)
		WHEN 3 THEN CONVERT(varbinary,Amount)
		       ELSE CONVERT(varbinary,OrderID)
	  END 
--FOR XML EXPLICIT 
go

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 10:34:13

GOT IT!!!!!

Light bulb ON..So that's what the column naming convention is for!

Don't think I'll ever use this...at least I hop not...But here you go:



DECLARE @sort tinyint 
SET @sort=0
SELECT * FROM (
   SELECT 1 AS Tag 
	, 0 AS Parent 
	, NULL AS [ROOT!1]
	, NULL AS [orders!2!OrderID]
	, NULL AS [orders!2!CompanyName]
	, NULL AS [orders!2!orderdate]
	, NULL AS [orderDet!3!Amount] 
	, NULL AS [orderDet!3!ProductName] 
UNION ALL 
   SELECT 2
	, 1
	, NULL
	, o.orderid
	, companyname
	, convert(varchar(10), orderdate, 101)
	, NULL
	, NULL 
     FROM Orders o 
     JOIN customers c ON o.customerid = c.customerid 
UNION ALL 
   SELECT 3 
	, 2 
	, NULL 
	, o.orderid 
	, companyname 
	, convert(varchar(10), orderdate, 101) 
	, d.Unitprice*quantity
	, productname 
     FROM orders o 
     JOIN [order details] d ON o.orderid = d.orderid 
     JOIN customers c 	    ON o.customerid = c.customerid 
     JOIN products p 	    ON d.productid = p.productid 
) AS xxx
 ORDER BY CASE @sort
		WHEN 0 THEN CONVERT(varbinary,[orders!2!CompanyName]) 
		WHEN 1 THEN CONVERT(varbinary,[orderDet!3!ProductName])
		WHEN 2 THEN CONVERT(varbinary,[orders!2!orderdate])
		WHEN 3 THEN CONVERT(varbinary,[orderDet!3!Amount])
		       ELSE CONVERT(varbinary,[orders!2!OrderID])
	  END 
FOR XML EXPLICIT 
go

Brett

8-)

LLatinsky
Starting Member

38 Posts

Posted - 2003-05-30 : 10:45:15

Thank you, everybody for your replies. The first part - doing a select* from a derived query worked = meaning I stopped getting the 'invalid column name' error - but I am still struggling with the ordering of the result set ( even after I added
'order by case
when @sort = 0 then [orders!2!CompanyName]
when @sort=1 then [orderDet!3!ProductName]
when @sort=2 then [orders!2!orderdate]
when @sort=3 THEN [orderDet!3!Amount]
END , [orders!2!OrderID] FOR XML EXPLICIT ' it doesn't seem to put some details under correct orders. I'll keep testing.
Thank you, Arnold, for the derived query idea.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 10:45:33

Oh, and by the way, Thanks for the education...

Brett

8-)

monkeybite
Posting Yak Master

152 Posts

Posted - 2003-05-30 : 10:58:32

quote:
it doesn't seem to put some details under correct orders

You need to sort by OrderId first, then the other items, in order to get the results to properly nest. I posted this a few months ago about XML EXPLICIT sorting:
[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=21812[/url]

monkey

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 11:44:22

quote:

derived query idea

Derived Table...still doesn't make sense to me...basically faking out the realtional engine...

And Yes ...THANKS ARNOLD

Brett

8-)

LLatinsky
Starting Member

38 Posts

Posted - 2003-05-30 : 13:08:37

Unfortunately, if you sort by orderid first than it sorts it by orderid first which conflicts with a dynamic order by.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 13:13:35

Do you need a composite order by clause? Like for example ordering by more than 1 thing? Alos why the exercise in dynamic ordering if you need a very specific ordering.

Brett

8-)

LLatinsky
Starting Member

38 Posts

Posted - 2003-05-30 : 13:16:28

I checked out the link to the topic on XML EXPLICIT provided. If I add PARENT, TAG at the end - it actually sorts it in a correct order. Thank you.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-30 : 13:19:23

Since I've been working on this for 2 days, could you post the code?

Brett

8-)

LLatinsky
Starting Member

38 Posts

Posted - 2003-05-30 : 13:26:47

Sure, here you go and thank you for your help

declare @sort tinyint
set @sort=0
SELECT * FROM(
SELECT 1 AS Tag,
NULL AS Parent,
NULL AS [ROOT!1],
NULL AS [orders!2!OrderID],
NULL AS [orders!2!CompanyName],
NULL AS [orders!2!orderdate],
NULL AS [orderDet!3!Amount],
NULL AS [orderDet!3!ProductName]
UNION ALL
SELECT 2,
1,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
NULL, NULL
from orders o join customers c on o.customerid = c.customerid
where companyname like 'Th%'
union all
SELECT 3,
2,
NULL,
o.orderid,
companyname,
convert(varchar(10), orderdate, 101),
convert(varchar(20),d.Unitprice*quantity),
productname
from orders o join [order details]d on o.orderid = d.orderid
join customers c on o.customerid = c.customerid
join products p on d.productid = p.productid
where companyname like 'Th%' )as Z
order by
case
when @sort = 0 then [orders!2!CompanyName]
when @sort=1 then [orderDet!3!ProductName]
when @sort=2 then [orders!2!orderdate]
when @sort=3 THEN [orderDet!3!Amount]
END ,[orders!2!OrderID],tag,parent FOR XML EXPLICIT
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources