FOR XML in vb.net OleDbCommand

Question

Hello experts

Is FOR XML valid in vb.net 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.

Thanks.

Answer 1

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";  
  
   try  
   {  
      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')";  
  
         con.Open();  
  
         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))  
         {  
            xdoc.Save(writer);  
         }  
         Console.WriteLine("File '{0}' has been created.", FILENAME);  
      }  
   }  
   catch (Exception ex)  
   {  
      Console.WriteLine("Connection failed with the following exception...");  
      Console.WriteLine(ex.Message.ToString());  
   }  
}