EASY, RIGHT? : Retrieving SQL Data as an XML Document

[samadams_2006]

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(B yVal sender As System.Object, ByVal e As

System.EventArg s) Handles
Button1.Click
Dim objConn As New OleDbConnection ( _
"Provider=MSDAO RA.1;User
ID=abc;DataSour ce=sdqa.ourcomp any.com;Passwor d=pwabc")

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

objConn.Open()
objCmd = New OleDbCommand("S elect * from z_test", objConn)
OleDbConnection 1.Open()
objRdr = objCmd.ExecuteR eader

While objRdr.Read
Response.Write( objRdr.Item("fi rstname") & _
"," & objRdr.Item("la stname") & _
"," & objRdr.Item("ci ty") & _
"," & objRdr.Item("st ate") & _
"<br />")
End While
objRdr.Close()

'doc = New XmlDocument
'doc.Load(Serve r.MapPath("chap ter8.xml"))

'trans = New XslTransform
'trans.Load(Ser ver.MapPath("ch apter8.xsl"))

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

End Sub

[Martin Honnen]

sa***********@y ahoo.ca wrote:

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

System.EventArg s) Handles
Button1.Click
Dim objConn As New OleDbConnection ( _
"Provider=MSDAO RA.1;User
ID=abc;DataSour ce=sdqa.ourcomp any.com;Passwor d=pwabc")

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/frlrfSystemData SqlClientSqlCom mandClassExecut eXmlReaderTopic .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 OleDbDataAdapte r()
adapter.SelectC ommand = new OleDbCommand("S elect * from z_test", conn)
adapter.Fill(da taset)
Dim dataDocument as XmlDataDocument = new XmlDataDocument (dataset)

Dim trans As XslTransform

'trans = New XslTransform
'trans.Load(Ser ver.MapPath("ch apter8.xsl"))

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
http://JavaScript.FAQTs.com/

[samadams_2006]

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=MSDAO RA.1;User ID=user1;Data
Source=sdqa.com pany.com;Passwo rd=password1")
Dim adapter As New OleDbDataAdapte r
adapter.SelectC ommand = New OleDbCommand("S elect firstname from
z_test", conn)
adapter.Fill(da taset)
Dim dataDocument As XmlDataDocument = New
XmlDataDocument (dataset)
=============== =============== =============== =============== =============== =========

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

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

Hmmm... When I create an OleDBConnection and an OleDBDataAdapte r
Object on the Web Form, and link them together, I'm able to bring up
the data when I "right-click" on the "QueryBuild er" 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...

[Martin Honnen]

sa***********@y ahoo.ca wrote:

Dim dataset As DataSet

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

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/

[samadams_2006]

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...

[Martin Honnen]

sa***********@y ahoo.ca wrote:

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

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(Ser ver.MapPath("ch apter8.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.Output Stream, Nothing)

is one possible way (With ASP you should also send Response.Conten tType
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.Conten tType = "applicatio n/xml"
dataDocument.Sa ve(Response.Out putStream)
to simply look in IE at the XML returned could help to enable you to
write a stylesheet.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/