select for xml

[rudba]

How do i use select for xml?


strSQL = "SELECT id, fname,lname FROM tbl1 FOR XML path('')"


Comm = New SqlCommand(strSQL, Con)
Comm.CommandType = CommandType.Text
Comm.ExecuteNonQuery()

 

[MattP]

This example should get you Xml out of SQL Server 2005+ and show you how to read Xml into a DataSet.

		Dim cn As New SqlConnection(My.Settings.MyConnectionString)
		Using cmd As New SqlCommand("SELECT Name, ProductNumber FROM Production.Product FOR XML AUTO", cn)
			cn.Open()
			Dim xr As Xml.XmlReader = cmd.ExecuteXmlReader()
			Dim ds As New DataSet
			ds.ReadXml(xr, XmlReadMode.InferSchema)
		End Using

Edit: AdventureWorks DB used in sample