Xml using SQLQueryData - help

  • Thread starter Thread starter Timothy V
  • Start date Start date
T

Timothy V

Hi,
I'm having trouble working this out and was wondering if anyone could help
me. I have a query:
SELECT
1 AS Tag,
NULL AS Parent,
classID AS [Class!1!classID],
className AS [Class!1!Name!element]
FROM Classes
FOR XML EXPLICIT;

The problem is that when i invoke this:
XmlReader xr = sqlCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
xd.Save(@"C:\a.xml");

I get this error:
"This document already has a DocumentElement node."

Can anyone tell me how to make this work? All I want to do is save the data
from the query to an xml file.

Thank you in advance,

Tim.
 
When you use XML AUTO and XML RAW, you end up with no root tag. You can
control this in XML EXPLICIT, but most examples do not. Not sure what you
are attempting here, as you are missing the union that matches the 1 and
NULL to child nodes.

Here is my advise.

1. Run your query in Query Analyzer
2. Copy the XML and either a) dump into a tool like XML Spy or b) clean it
up in Word or notepad
3. See if there is a root tag.

If not, you will have to:
a) create the root tag in your XML EXPLICIT command (OR)
b) Add a root tag in a stored procedure that call this command (OR)
c) Add a root tag in your code - requires dumping the XmlReader and moving
to SqlReader (OR)
d) Access SQL Server through an XML template that supplies root

Examining the XML will help.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Cowboy (Gregory A. Beamer) said:
If not, you will have to:
a) create the root tag in your XML EXPLICIT command (OR)
b) Add a root tag in a stored procedure that call this command (OR)
c) Add a root tag in your code - requires dumping the XmlReader and moving
to SqlReader (OR)
d) Access SQL Server through an XML template that supplies root
Click to expand...

It doesn't necessarily mean dumping XmlReader. You could write a class
which derives from XmlReader, proxies all but the start and end to the
one returned by ExecuteXmlReader() and provides the appropriate start
and end tags itself. There's quite a lot to override, admittedly, but
it would certainly be possible.
 
I will stand corrected and concur with your correction.

The only issue I can see with this direction is the user's experience with
subclassing, as the "out of the box" XmlReader will not work without a root
tag.

As I see it (in order of ease of coding):
---------------------------------------
1. Add root tag in SQL Template (requires IIS)
2. Add root tag in SQL (variety of ways)
3. Add root tag in code and scrap XmlReader
4. Derive own XmlReader from Microsoft classes

In the long run, if the poster is going to continue to work with no root
tag, the override would get him the most mileage, so it is as good an idea
as the rest, if this is his modus operandi.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Thank you for all your information. I don't know how I'd learn to program
without you guys!

Thank you again.


Cowboy (Gregory A. Beamer) said:
I will stand corrected and concur with your correction.

The only issue I can see with this direction is the user's experience with
subclassing, as the "out of the box" XmlReader will not work without a
root
tag.

As I see it (in order of ease of coding):
---------------------------------------
1. Add root tag in SQL Template (requires IIS)
2. Add root tag in SQL (variety of ways)
3. Add root tag in code and scrap XmlReader
4. Derive own XmlReader from Microsoft classes

In the long run, if the poster is going to continue to work with no root
tag, the override would get him the most mileage, so it is as good an idea
as the rest, if this is his modus operandi.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
Click to expand...
 
Well, I knew there wasn't a root tag and that it was the source of the
problem. The trouble is, I'm not entirely sure how to do ur advice for 3a
(ie. create the root tag in your XML EXPLICIT command).

I don't think i have enough knowledge about sqlquery-xml in order to do. I
guess what i'm asking is, how do you do it? :-)

Thanks again.


Cowboy (Gregory A. Beamer) said:
When you use XML AUTO and XML RAW, you end up with no root tag. You can
control this in XML EXPLICIT, but most examples do not. Not sure what you
are attempting here, as you are missing the union that matches the 1 and
NULL to child nodes.

Here is my advise.

1. Run your query in Query Analyzer
2. Copy the XML and either a) dump into a tool like XML Spy or b) clean it
up in Word or notepad
3. See if there is a root tag.

If not, you will have to:
a) create the root tag in your XML EXPLICIT command (OR)
b) Add a root tag in a stored procedure that call this command (OR)
c) Add a root tag in your code - requires dumping the XmlReader and moving
to SqlReader (OR)
d) Access SQL Server through an XML template that supplies root

Examining the XML will help.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
Timothy V said:
Hi,
I'm having trouble working this out and was wondering if anyone could
help
me. I have a query:
SELECT
1 AS Tag,
NULL AS Parent,
classID AS [Class!1!classID],
className AS [Class!1!Name!element]
FROM Classes
FOR XML EXPLICIT;

The problem is that when i invoke this:
XmlReader xr = sqlCommand.ExecuteXmlReader();
XmlDocument xd = new XmlDocument();
xd.Load(xr);
xd.Save(@"C:\a.xml");

I get this error:
"This document already has a DocumentElement node."

Can anyone tell me how to make this work? All I want to do is save the data
from the query to an xml file.

Thank you in advance,

Tim.
Click to expand...
Click to expand...
 
Back
Top