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

MS SQL 2000 - SQL Select with XPATH Where Clause

In MS SQL Server 2000, is there an easy way to use an XPATH statement in a
WHERE clause? I have a table with a TEXT column that contains XML. I would
like to query this table and select any rows where the XML data field meets
my XPATH criteria.
I'm attempting to avoid using cursors and sp_xml_preparedocument on each row
of data in the table, but with SQL Server 2000, that may be my only option.
Any help would be appreciated!Bryan (Bryan@.discussions.microsoft.com) writes:
> In MS SQL Server 2000, is there an easy way to use an XPATH statement in
> a WHERE clause?
Yeah, upgrade the instance to SQL 2005. :-)

> I have a table with a TEXT column that contains XML. I
> would like to query this table and select any rows where the XML data
> field meets my XPATH criteria.
> I'm attempting to avoid using cursors and sp_xml_preparedocument on each
> row of data in the table, but with SQL Server 2000, that may be my only
> option.
OPENXML is on the only XML support that SQL 2000 offers. I agree that a
cursor and preparedocument for each is not appealing. The other alternative
would be to bring the data to the client, but is probably even less
appealing.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx