MS SQL Server XML Query Help

[Jay]

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 [RepurchaseDetai ls!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 RepurchaseDetai ls to RepurchaseDetai lzzz
the query works just fine. It however still fails for
RepurchaseDetai lz, or RepurchaseDetai lzz

Any ideas???

[Simon Hayes]

Re: MS SQL Server XML Query Help


"Jay" <xml_@hotmail.c om> wrote in message
news:4e7ac2ff.0 402260651.7c8b8 88b@posting.goo gle.com...[color=blue]
> 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 [RepurchaseDetai ls!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 RepurchaseDetai ls to RepurchaseDetai lzzz
> the query works just fine. It however still fails for
> RepurchaseDetai lz, or RepurchaseDetai lzz
>
> Any ideas???[/color]

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

Simon

[Erland Sommarskog]

Re: MS SQL Server XML Query Help

Jay (xml_@hotmail.c om) writes:[color=blue]
> 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 [RepurchaseDetai ls!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 RepurchaseDetai ls to RepurchaseDetai lzzz
> the query works just fine. It however still fails for
> RepurchaseDetai lz, or RepurchaseDetai lzz[/color]

Not only that. Shortening it to RepurchaseDetai l 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/techinfo/productdoc/2000/books.asp

[Jay]

Re: MS SQL Server XML Query Help

Thanks, I posted this same question on microsoft.publi c.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:<Xns949C6D 4ED1DBYazorman@ 127.0.0.1>...[color=blue]
> Jay (xml_@hotmail.c om) writes:[color=green]
> > 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 [RepurchaseDetai ls!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 RepurchaseDetai ls to RepurchaseDetai lzzz
> > the query works just fine. It however still fails for
> > RepurchaseDetai lz, or RepurchaseDetai lzz[/color]
>
> Not only that. Shortening it to RepurchaseDetai l 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.[/color]

[Erland Sommarskog]

Re: MS SQL Server XML Query Help

Jay (xml_@hotmail.c om) writes:[color=blue]
> Thanks, I posted this same question on microsoft.publi c.sqlserver.xml
> and they are stumped. I have tried this on different versions of SQL
> server 2000 with the same results.[/color]

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/techinfo/productdoc/2000/books.asp

[Jay]

Re: MS SQL Server XML Query Help

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:<Xns949CEE CB08C61Yazorman @127.0.0.1>...[color=blue]
> Jay (xml_@hotmail.c om) writes:[color=green]
> > Thanks, I posted this same question on microsoft.publi c.sqlserver.xml
> > and they are stumped. I have tried this on different versions of SQL
> > server 2000 with the same results.[/color]
>
> 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.[/color]

[Erland Sommarskog]

Re: MS SQL Server XML Query Help

Jay (xml_@hotmail.c om) writes:[color=blue]
> 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.[/color]

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/techinfo/productdoc/2000/books.asp