Hello,

How do I retrieve SQL Data into an XML Document? I have the following
code, which will retrieve SQL data and write it to the screen via the
Response Object, but I'd like to be able to read it as XML Data, and
use XSL and XSLT to display the data.

Any suggestions? Code Snippets MORE than appreciated... :)

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


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As

System.EventArgs) Handles
Button1.Click
Dim objConn As New OleDbConnection( _
"Provider=MSDAORA.1;User
ID=abc;DataSource=sdqa.ourcompany.com;Password=pwabc")

Dim objCmd As OleDbCommand
Dim objRdr As OleDbDataReader
Dim doc As XmlDocument
Dim trans As XslTransform

objConn.Open()
objCmd = New OleDbCommand("Select * from z_test", objConn)
OleDbConnection1.Open()
objRdr = objCmd.ExecuteReader

While objRdr.Read
Response.Write(objRdr.Item("firstname") & _
"," & objRdr.Item("lastname") & _
"," & objRdr.Item("city") & _
"," & objRdr.Item("state") & _
"<br />")
End While
objRdr.Close()

'doc = New XmlDocument
'doc.Load(Server.MapPath("chapter8.xml"))

'trans = New XslTransform
'trans.Load(Server.MapPath("chapter8.xsl"))

'Xml1.Document = doc
'Xml1.Transform = trans

End Sub

Hi Martin,

Thanks for the help. I've tried your snippet of code and it compiles
fine... Here's what I've tried:

====================================================================================
Dim dataset As DataSet
Dim conn As New OleDbConnection( _
"Provider=MSDAORA.1;User ID=user1;Data
Source=sdqa.company.com;Password=password1")
Dim adapter As New OleDbDataAdapter
adapter.SelectCommand = New OleDbCommand("Select firstname from
z_test", conn)
adapter.Fill(dataset)
Dim dataDocument As XmlDataDocument = New
XmlDataDocument(dataset)
====================================================================================

Unfortunately, now I'm getting a "run time" error on the line
"adapter.Fill(dataset)". The error states:

Exception Details: System.ArgumentNullException: Value cannot be null.
Parameter name: dataSet

Hmmm... When I create an OleDBConnection and an OleDBDataAdapter
Object on the Web Form, and link them together, I'm able to bring up
the data when I "right-click" on the "QueryBuilder" and do "Run" on the
bottom table. This tells me that the program is able to connect to the
table and retrieve the information, at least via the GUI. For some
reason it will not work via this code.

Any ideas on what the problem could be, or how I could narrow this
problem down further?

Thanks a Bunch...

Hi Martin,

Soooo Close. That worked, now it's gone to the statement:

trans.Transform(dataDocument, other arguments)

I've tried:

trans.Transform(dataDocument, Server.MapPath("chapter8.xsl"))

etc., etc.

It doesn't like the dataDocument. It gives about 18 separate method
calls, and this first parameter needs to be one of :

XPathNavigator
IXPathNavigable

Any ideas?

Thanks...

[quoted text, click to view]


[quoted text, click to view]

If you use SQL server then you can generate XML with a FOR XML query and
use the ExcuteXmlReader method
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassExecuteXmlReaderTopic.asp>

If you want or need to use OleDb then I think one way is as follows
which gets the result of the query in a DataSet and then loads the
DataSet in an XmlDataDocument which can be used for XSLT transformations:

Dim dataset As DataSet
Dim conn As New OleDbConnection(connection)
Dim adapter As New OleDbDataAdapter()
adapter.SelectCommand = new OleDbCommand("Select * from z_test", conn)
adapter.Fill(dataset)
Dim dataDocument as XmlDataDocument = new XmlDataDocument(dataset)


[quoted text, click to view]

Then here you can pass dataDocument as the first parameter to the
Transform method of the XslTransform trans object
trans.Transform(dataDocument, other arguments)
where other arguments obviously depends on which overload of the
Transform method you want to use.

My code snippets above are typed here in that post so don't expect that
to compile as typed, but you should get the idea on which objects to use.

Give some feedback on whether the outlined approach works for you.



--

Martin Honnen --- MVP XML

[quoted text, click to view]


[quoted text, click to view]

Does it work if you do
Dim dataset As DataSet = new DataSet()
here before passing dataset to the Fill method?


--

Martin Honnen --- MVP XML

[quoted text, click to view]


[quoted text, click to view]

I had hoped you had used the Transform method before and know what other
parameters you need. As for dataDocument, it is an XmlDataDocument which
implements IXPathNavigable so any Transform overload that takes an
IXPathNavigable as the first arguments will consume the dataDocument as
the first argument.
Assuming you have (with comments removed)

'trans = New XslTransform
'trans.Load(Server.MapPath("chapter8.xsl"))

as in your first post you have to decide what kind of transformation
result you want, if that is ASP.NET and you want to send the result of
the transformation to the user agent as the HTTP response then doing e.g.

trans.Transform(dataDocument, Nothing, Response.OutputStream, Nothing)

is one possible way (With ASP you should also send Response.ContentType
as needed if your stylesheet does not create HTML). And of course your
stylesheet chapter8.xsl that you load with XslTransform is crucial to
get any meaningful results, the DataSet/XmlDataDocument convert the
relational query result to XML but obviously if you want to write a
stylesheet to process that XML then you need to know how the XML looks.

So for a test not doing any transform but rather simply
Response.ContentType = "application/xml"
dataDocument.Save(Response.OutputStream)
to simply look in IE at the XML returned could help to enable you to
write a stylesheet.



--

Martin Honnen --- MVP XML