Create XML from database table in VB

[amberdextrous]

I have a problem creating an XML file from a SQL Server 2005 table. This is all within a VB 2005 program I'm making. I run a query against the table and store the results in a dataset. Then I call

objDataSet.WriteXML()

Unfortunately, the table is poorly designed. All the columns have spaces in the names, so when the XML file is generated, you see a lot of this:

<Agency_x00A0_code>yadayada</Agency_x00A0_code>

This looks quite bad when you have 20 columns and 30 records with these messy element tags. I'm trying to convince my manager to change the column names, but in the event he won't do it...

Is there a way I can specify in my VB code to replace blank spaces with underscores (_) in my XML documents' element tags? This way it won't insert "_x00A0_" whereever there's a space.

 

[RiverGuy]

You can try changing the column names in the datatable

For Each dc As DataColumn In YourDataTable.Columns
  If dc.ColumnName.Contains(" ") Then
    dc.ColumnName = dc.ColumnName.Replace(" ", "_")
  End If
Next

 

[bboffin]

Alternatively you can give aliases to all the columns in your SQL query and then derive your dataset from the results of the query.

Just remember that you'll have to hand code any Insert or Delete commands as the dataset won't map them correctly.

Incidentally this also applies to renaming the columns in the dataset.

Bob Boffin

 

[amberdextrous]

Thanks guys, I talked to my manager and ended up going more with Bob's suggestion. I took the query out of my VB code, and instead made a stored procedure in SQL Server with it, and gave column aliases to all the columns. At least that way after we deploy the application, if we find that we have to add more columns later, we can just edit the stored procedure without having to edit the VB code and re-deploy the application.

The whole thing with me wanting to get my VB code to replace spaces with underscores was because I was doing a SELECT * statement, so even if we added columns in the database later we wouldn't have to edit the VB code. That's why I wanted to stay away from column aliases, because then we would have to edit the code whenever we add a column and re-deploy the app. Going with the stored procedure seemed to be the best compromise.