Return XML

[venerable bede]

The following function is used in my webservice which returns a dataset.
I now need to return the dataset as xml and have no idea how to do it.
Can anyone help ?
Thanks in Advance

Private Function GetDataSetXML(ByVal strSQL As String) As dataset
        '1. Create a connection
        Dim connString As String
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\blobdata\data.mdb" & ";"
        Dim myConnection As New OleDbConnection(connString)
        '2. Create the command object, passing in the SQL string    
        Dim myCommand As New OleDbCommand(strSQL, myConnection)
        myConnection.Open()
        '3. Create the DataAdapter
        Dim myDataAdapter As New OleDbDataAdapter()
        myDataAdapter.SelectCommand = myCommand
        '4. Populate the DataSet and close the connection
        Dim myDataSet As New DataSet()
        myDataAdapter.Fill(myDataSet)
        myConnection.Close()
        'Return the DataSet
        Return myDataset
    End Function

[Memnoch1207]

Try using the SQLXML class, which allows you to pull the data in XML format from any relational database.

Here's a link

[venerable bede]

Thanks.

I'll give it a go.

[hellswraith]

You can also use the "FOR XML" clause in your sql statment that is returning the data. Sql will do the xml conversion for you.

Or

You can use the dataset and use its .WriteXML method to get the xml.

[Memnoch1207]

"FOR XML" doesn't work with all relational databases does it???
I know it works with SQL Server 2000, because it has built-in XML support, but does it work with Access or Oracle, etc...??

[hellswraith]

Your right...sorry

I was thinking the poster was using SQL Server when I was writing that...

[venerable bede]

Yeah, I'm using a crappy access back-end so the SQL XML is out of the question.

I tried the .getxml but it creates one long string. Unfortunatly I'm writing this service for a graphics guy and he says its of no use to him.

Bloody graphic designers.

[venerable bede]

Do either of you guys know where I can find an example of populating an array from a dataset.

He's just told me that he now wants an array !!!!

Bloody Designers !!!!!

[Memnoch1207]

The SQLXML Class will work with any relational database.
It's the FOR XML clause that only works with SQL Server 2000.