Very Computer

by **Erik Johnso** » Mon, 05 Nov 2001 02:56:23

This issue was posted recently, but I couldn't use the solution. I am
having the same problem in that the XML returned by SQL2000 as a .NET
XmlReader can't get loaded into an XmlDocument object because is has no
single root. Here is a sample query:

SELECT 1 AS Tag,
NULL AS Parent,
Orders.OrderID AS [Orders!1!OrderID],
NULL AS [OrderDetails!2!ProductID]
FROM Orders

UNION ALL

SELECT 2,
1,
Orders.OrderID,
[Order Details].ProductID

FROM Orders, [Order Details]
WHERE Orders.OrderID = [Order Details].OrderID
ORDER BY [Orders!1!OrderID], [OrderDetails!2!ProductID]

FOR XML EXPLICIT

The question is: What do I need to add to this query to get a single root
element (say, <Root>[Results]</Root>)? Alternatively, is there an easy way
to establish a root element in mhy XmlDocument object before loading the
results from the XmlReader? Thanks very much. Any help is appreciated.

- Erik Johnson

by **Bryant Like** » Mon, 05 Nov 2001 11:49:14

Quote:>-----Original Message-----
>This issue was posted recently, but I couldn't use the

solution.

Does the solution in this FAQ not solve the problem?

http://sqlxml.org/sqlxml/faqs.xml?faq=16

If that doesn't work let me know (I guess I am going to
have to install VS.Net <g>).

--
Bryant

by **Erik Johnso** » Tue, 06 Nov 2001 01:13:14

Quote:> >-----Original Message-----
> >This issue was posted recently, but I couldn't use the
> solution.

> Does the solution in this FAQ not solve the problem?

> http://sqlxml.org/sqlxml/faqs.xml?faq=16

Thanks for the reply. The solution described in the FAQ only works for
stored procedures. If I use that solution in an ad-hoc query (using a
SqlCommand object to send the SQL to the DB and the ExecuteXmlReader
method), I get an exception:

"Invalid command sent to ExecuteXmlReader. The command must return an Xml
result."

I don't think it likes the multiple SELECT statements that are not bound by
a UNION. The alternative solution mentioned in the FAQ (specifying the root
tag in the URL) only works when using SQL Server via IIS. I'm working to
better understand FOR EXPLICIT, but in the mean time any help is greatly
appreciated. Thanks,

- Erik

by **Bryant Like** » Tue, 06 Nov 2001 02:17:08

[snip]

Quote:> I don't think it likes the multiple SELECT statements that are not bound
by
> a UNION. The alternative solution mentioned in the FAQ (specifying the
root
> tag in the URL) only works when using SQL Server via IIS. I'm working to
> better understand FOR EXPLICIT, but in the mean time any help is greatly
> appreciated. Thanks,

What if you send your ad hoc query using this type of method:

http://www.sqlxml.org/sqlxml/faqs.xml?faq=1

Since the results are streamed there has to be a way to do this....

--
Bryant

by **Dan Farin** » Wed, 07 Nov 2001 09:34:27

I think this will only work in VB6. In .NET, you can use SQLXML 2.0, which
has a set of managed classes that let you do XML template queries and/or
specify the Root element.

--
Dan Farino
Sr. Systems Programmer
Stamps.com, Inc., Santa Monica, CA

> [snip]
> > I don't think it likes the multiple SELECT statements that are not bound
> by
> > a UNION. The alternative solution mentioned in the FAQ (specifying the
> root
> > tag in the URL) only works when using SQL Server via IIS. I'm working
to
> > better understand FOR EXPLICIT, but in the mean time any help is greatly
> > appreciated. Thanks,

> What if you send your ad hoc query using this type of method:

> http://www.sqlxml.org/sqlxml/faqs.xml?faq=1

> Since the results are streamed there has to be a way to do this....

> --
> Bryant

Very Computer

Adding a literal root element.

Adding a literal root element.

Adding a literal root element.

Adding a literal root element.

Adding a literal root element.

Adding a literal root element.