for xml raw with ado.net

[amitguitarplayer]

hi guys ,



i have a select query "Select * from DatTable for XML raw"

i get the results in an xml format, please help me with what i need to use to retrieve the data. i tried using command.executeXmlReader() but does not work ! i need the xml string(results) that is returned when i execute the query



please help !



Thank you

 

[MattP]

Try this:

		Dim cn As SqlConnection = New SqlConnection(My.Settings.MyConnectionString)
		Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM table FOR XML RAW", cn)

		cn.Open()
		Dim xr As XmlReader = cmd.ExecuteXmlReader()
		Dim XmlResults As String = String.Empty

		xr.Read()
		Do While xr.ReadState <> ReadState.EndOfFile
			XmlResults &= xr.ReadOuterXml
		Loop

		cn.Close()

 

[amitguitarplayer]

Hi matt,
i already did try your method, but i have 5400 rows and lopping 5400 times is not what i really want, is there a way that i can just get the entire result returned from the query ? instead of looping and getting one row at a time ?

 

[MattP]

You'll need to have SqlXml installed for this one.

Dim cmd As SqlXmlCommand = New SqlXmlCommand("Provider=SQLOLEDB;Server=YourServer;Database=YourDatabase;Integrated Security=SSPI")
cmd.CommandType = SqlXmlCommandType.Sql
cmd.CommandText = "SELECT * FROM YourTableFOR XML RAW"

Dim sr As StreamReader = New StreamReader(cmd.ExecuteStream)
Dim XmlString As String = sr.ReadToEnd()

Download details: SqlXml 3.0 Service Pack 3 (SP3)