sql-serverxmlexport-to-xml

Exporting SQL Server table to XML

I'am trying to export SQL Server table to XML file using the following code:

Dim ds As New Data.DataSet("MyData")   
Dim QueryStr As String = "Select * from Inventory" 
Dim ConnectionStr as String = "data source=SQLSERVER\ITDB;integrated..."
Dim da As New System.Data.SqlClient.SqlDataAdapter(QueryStr,ConnectionStr)
da.Fill(ds)
da.Dispose()
ds.WriteXml("c:\output.xml")

The output format of the XML file is like :

<MyData>
<Table>
  <IPAddress>192.168.0.193</IPAddress>
  <UserName>Admin</UserName>
  <Location>Store</Location>
</Table>
</MyData>

I want to change the XML file format to be like:

<MyData>
<Table IPAddress="192.168.0.193" UsersName="1001" Location="Store" />
</MyData>

I need to know what is the name of this format. This format is valid as XMLDataSource within ASPxGriveView.

Solution

  • Set the Datacolumn ColumnMapping property to MappingType.Attribute for each column you want to be exported as an XML attribute.

    dataTable = dataSet.Tables["SomeTable"];

foreach (DataColumn dc in dataTable.Columns)
{
    dc.ColumnMapping = MappingType.Attribute;
}