Link to home
Start Free TrialLog in
Avatar of doctor069
doctor069Flag for Canada

asked on

XML To Dataset

Hi-

I have a sql SP that returns XML

SELECT TOP 10
       [Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[CompanyName]    
  FROM [AdventureWorksLT2008].[SalesLT].[Customer]
ORDER BY
   [FirstName], [LastName]
FOR XML AUTO, ROOT('customer')

I need to get this into datatable but the code below does not seen to work!

   Dim sqlConnection1 As New SqlConnection("Data Source=XXX; Database=AdventureWorksLT2008; User ID=XXX;Password=XXX;")
        Dim cmd As New SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet("customer")
        cmd.CommandText = "SimpleSelectXML"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = sqlConnection1
        Try
            sqlConnection1.Open()
            Dim xr As XmlReader = cmd.ExecuteXmlReader()
            ds.ReadXml(xr.Read)
            cmd.Dispose()
            gv1.DataSource = ds
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            sqlConnection1.Close()
        End Try

Help would be appreciated!

Thanks in advance
ASKER CERTIFIED SOLUTION
Avatar of Obadiah Christopher
Obadiah Christopher
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of doctor069

ASKER

Yes!
Thank you very much