FOR XML in OleDbCommand

OSVBNET 1,381 Reputation points

Hello experts

Is FOR XML valid in working with OleDbCommand? If not possible to use it as below somehow?

Dim MyCommand As New OleDbCommand("SELECT * FROM Accounts WHERE AccountName = '" + DataGridView.CurrentRow.Cells.Item(0).Value.ToString.Replace("'", "''") + "' FOR XML RAW", MyConnection)

*** I need to export a data row as selected above into an xml file.


An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,304 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Yitzhak Khabinsky 23,231 Reputation points

    Hi @OSVBNET ,

    Here is a complete working example for you.
    It is in c#, but you can easily convert it to VB.NET.

    void Main()  
       const string FILENAME = @"e:\temp\file_OLEDB.xml";  
          OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();  
          builder.Provider = "MSOLEDBSQL.1";  
          builder.DataSource = @"yourDataSource";  
          builder.Add("Initial Catalog", "AdventureWorks2019");  
          builder.Add("Integrated Security", "SSPI");  
          using (OleDbConnection con = new OleDbConnection(builder.ConnectionString))  
          using (OleDbCommand cmd = new OleDbCommand())  
             // dynamic SQL  
             cmd.Connection = con;  
             cmd.CommandType = CommandType.Text;  
             cmd.CommandText = "SELECT  * FROM Person.Person FOR XML PATH('r'), TYPE, ROOT('root')";  
             XDocument xdoc = XDocument.Parse((string)cmd.ExecuteScalar());  
             var settings = new XmlWriterSettings();  
             settings.Indent = true;  
             settings.OmitXmlDeclaration = false;  
             settings.IndentChars = "\t";  
             // to remove BOM  
             settings.Encoding = new UTF8Encoding(false);  
             using (var writer = XmlWriter.Create(FILENAME, settings))  
             Console.WriteLine("File '{0}' has been created.", FILENAME);  
       catch (Exception ex)  
          Console.WriteLine("Connection failed with the following exception...");