Error with SqlSmlCommand

  • Thread starter Thread starter DragonslayerApps
  • Start date Start date
D

DragonslayerApps

I am attempting to read XML data directly from a table in SQL Server
2000 using VB.Net 2003. When I run the code shown below, I get the
following error:

"SQLXML: invalid direct object (dbobject) query -><-"

I applied SqlXml 3.0 Service Pack 3 to both client and server. My
project references Microsoft.Data.SqlXml. Client is Windows XP Pro,
Server is Windows Server 2003 with SQL Server 2000. All service packs
and patches are current.

'----------------------------------------------------------------------------
Imports Microsoft.Data.SqlXml

Private Sub ReadXML()

Dim CS As String = "Provider=SQLOLEDB; Data Source=MyServer;
Initial Catalog=MyDB; User Id=MyUser; Password=MyPassword"

Try

Dim cmd As New SqlXmlCommand(CS)

cmd.CommandType = CommandType.Text
cmd.CommandText = "Select parent_ID from tblData _
FOR XML AUTO, ELEMENTS"

Dim xr As XmlReader

xr = cmd.ExecuteXmlReader '<------Error happens here

xr.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
Finally

End Try
End Sub

Can anyone figure out what I'm missing? Specifying column names in the
SQL also has no effect. I tried adding a root tag to the command using
the code below before executing the reader but it had no effect.

cmd.RootTag = "xmlns:sql=""urn:schemas-microsoft-com:xml-sql"""

Thanks,

John
 
Hi John,

I suspect there might be some incompability with SqlXml here. If you change
to System.Data.SqlClient.SqlCommand and use SqlCommand.ExecuteXmlReader,
will this happen again?

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Kevin said:
Hi John,

I suspect there might be some incompability with SqlXml here. If you change
to System.Data.SqlClient.SqlCommand and use SqlCommand.ExecuteXmlReader,
will this happen again?

Kevin Yu
Microsoft Online Community Support
Click to expand...

Hi Kevin, thanks for your response.
If I use a SQLCommand object the process succeeds as far as populating
the reader.
An additional problem comes up after that though, when I try to Load an
XMLDocument from the reader, using the following line:

---------------------------------------------------------------------
Dim xd As XmlDocument = New XmlDocument
xd.Load(xr)
---------------------------------------------------------------------
The error is: "The document already has a DocumentElement node."

As I understand it, this means that the Reader is returning a set
without a root element, and so the Document reads the first element as
the root and the second (peer) element as a second root, which causes
the error. I was hoping that using the SQLXMLCommand would overcome
this.

How do I load this document?

John
 
Hi John,

Yes, as you know, it is because the returned xml doc doesn't have a root
node. You have to go through the xml with this XmlReader and put everything
into an XmlDocument under its root nodes.

Is there any particular reason that you must put the xml into an
XmlDocument? Maybe we can find a workaround for it.

Kevin Yu
Microsoft Online Community Support

==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Kevin~

I have an existing method which takes an XML Node List as a parameter.
I have been reading an XML file into an XML Document and extracting the
Node List from there. I am attempting to translate the same
functionality using a direct call to the database instead of the XML
file.

Thank you again for your help.

~John
 
Hi John,

If you're simply extracting the node list from the returned XML, I think
you can just go through each node of the xml doc with the returned
XmlReader. You will gain much better performance than first load it to
XmlDocument then extract a node list.

If you first load it to XmlDocument, actually, you will need to go through
the whole document twice.

Kevin Yu
Microsoft Online Community Support
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Kevin~

I'm sorry, I'm confused by your response. You say to "go through each
node of the XML doc with the returned XmlReader." This sounds like you
mean to add each node to the XML doc using a Do...Loop While
XMLReader.Read(). But then your other statements say not to use an
XMLDocument at all.

Thank you for your help.

~John
 
Hi John,

Sorry for the confusing. Yes, as you know, in my previous post, I meant
that you can use a do...loop to put all the content into XmlDocument.

However, it will be very slow, because then you need to go through the
XmlDocument again to get the data into your desired format. That make your
app parse the xml doc twice. So my suggestion is to manipulate on the
XmlReader directly to get the data into your desired format, you will only
need to go once.

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)
 
Back
Top