MS SQL Server XML Query Help

Hi all--

I am trying to execute the following query in MS SQL Server 2000.

SELECT 1 AS Tag, NULL as parent,
'x' AS [A!1!B],
NULL AS [C!48],
NULL AS [RepurchaseDetails!49]

UNION ALL

SELECT 48, 1,
NULL, 'y', NULL

UNION ALL

SELECT 49, 48,
NULL, NULL, 'z'

ORDER BY
TAG ASC

FOR XML EXPLICIT

I get the error:

Server: Msg 6806, Level 16, State 2, Line 1
Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.

However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
the query works just fine. It however still fails for
RepurchaseDetailz, or RepurchaseDetailzz

Any ideas?"Jay" <xml_@.hotmail.com> wrote in message
news:4e7ac2ff.0402260651.7c8b888b@.posting.google.c om...
> Hi all--
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1,
> NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48,
> NULL, NULL, 'z'
> ORDER BY
> TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz
> Any ideas?

You may want to post this in microsoft.public.sqlserver.xml, as you'll
probably get a better response there.

Simon|||Jay (xml_@.hotmail.com) writes:
> I am trying to execute the following query in MS SQL Server 2000.
> SELECT 1 AS Tag, NULL as parent,
> 'x' AS [A!1!B],
> NULL AS [C!48],
> NULL AS [RepurchaseDetails!49]
> UNION ALL
> SELECT 48, 1, NULL, 'y', NULL
> UNION ALL
> SELECT 49, 48, NULL, NULL, 'z'
> ORDER BY TAG ASC
> FOR XML EXPLICIT
> I get the error:
> Server: Msg 6806, Level 16, State 2, Line 1
> Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> the query works just fine. It however still fails for
> RepurchaseDetailz, or RepurchaseDetailzz

Not only that. Shortening it to RepurchaseDetail also works.

I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
bug long way.

The hour is a little late for me now, but I will look into this
again tomorrow. I guess that you need to use a workaround.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, I posted this same question on microsoft.public.sqlserver.xml
and they are stumped. I have tried this on different versions of SQL
server 2000 with the same results.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949C6D4ED1DBYazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > I am trying to execute the following query in MS SQL Server 2000.
> > SELECT 1 AS Tag, NULL as parent,
> > 'x' AS [A!1!B],
> > NULL AS [C!48],
> > NULL AS [RepurchaseDetails!49]
> > UNION ALL
> > SELECT 48, 1, NULL, 'y', NULL
> > UNION ALL
> > SELECT 49, 48, NULL, NULL, 'z'
> > ORDER BY TAG ASC
> > FOR XML EXPLICIT
> > I get the error:
> > Server: Msg 6806, Level 16, State 2, Line 1
> > Undeclared tag ID 49 is used in a FOR XML EXPLICIT query.
> > However if I change the tag RepurchaseDetails to RepurchaseDetailzzz
> > the query works just fine. It however still fails for
> > RepurchaseDetailz, or RepurchaseDetailzz
> Not only that. Shortening it to RepurchaseDetail also works.
> I need to brush up my knowledge about FOR XML EXPLICIT, but this smells
> bug long way.
> The hour is a little late for me now, but I will look into this
> again tomorrow. I guess that you need to use a workaround.|||Jay (xml_@.hotmail.com) writes:
> Thanks, I posted this same question on microsoft.public.sqlserver.xml
> and they are stumped. I have tried this on different versions of SQL
> server 2000 with the same results.

I've now tested the query on Yukon Beta 1, where it works as it should.

I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
I get the same error.

I will report this to Microsoft, and I would expect them to file a bug
unless this is a known problem.

However, if you are in need of a fix, and cannot use the workaround of
shortening the element name, or making it longer, then need to open a
case with Microsoft to pursue this.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for all your help, Microsoft has now confirmed to me that this
is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
and is now being considered for a future service pack for 2000. I'll
just have to work around it for now.

J

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns949CEECB08C61Yazorman@.127.0.0.1>...
> Jay (xml_@.hotmail.com) writes:
> > Thanks, I posted this same question on microsoft.public.sqlserver.xml
> > and they are stumped. I have tried this on different versions of SQL
> > server 2000 with the same results.
> I've now tested the query on Yukon Beta 1, where it works as it should.
> I have also tried it on build 8.00.859, a hotfix to SQL 2000. There
> I get the same error.
> I will report this to Microsoft, and I would expect them to file a bug
> unless this is a known problem.
> However, if you are in need of a fix, and cannot use the workaround of
> shortening the element name, or making it longer, then need to open a
> case with Microsoft to pursue this.|||Jay (xml_@.hotmail.com) writes:
> Thanks for all your help, Microsoft has now confirmed to me that this
> is a bug in SQL Servers FOR XML EXPLICIT mode that is fixed in Yukon,
> and is now being considered for a future service pack for 2000. I'll
> just have to work around it for now.

Thanks for reporting back! If this is known bug, then I don't need to
bug (sic!) my contacts at MS with it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp