When using the getXML() method of a dataset, I have noticed that every field in a column is null, the XML produced does not have any elements that correspond to the empty column. EG. table structure: col1 : A value col2 : NULL col3 : ANother value XML produced:
A value
ANother value
Is there an easy way to ensure that the null column is represented in the XML (in the above example, col2) ? Cheers Mark
I found a solution to it, but I'm not sure if it's the most efficient I used templates in an XSL file to transform the dataset so that even if there is not data, the xml elements are still created. The code I use to apply the xsl file and then return the result
as a string can be seen here http://www.gloshweb.co.uk/codedetail.aspx?codeid=3 Cheers Mark
One way is to use CASE in the SQL query. But thats not an option if you´re dealing with relational data (in the dataset). I´m also on the lookout for a good solution to this problem. peace.
I havn't found anything better than using XSL to ensure a standard output. Here is an example of the XSL I am using.
The second two "template match" calls allow me use the same template against two different datasets so tha I can produce the same output from two differing sources. the elements in the template (demo) need to reflect your required dataset, in most cases these
will be the columns of your dataset that you wish to make available, in this case mycol1 & mycol2 Element name is what will be returned in the XML (), the value of the select attribute is the name of the column from which you want to populate the XML element.
The above code will produce the following XML, regardless of any nulls in the underlying data.
That's all really great, but what if you don't know how many columns you are going to have? What if you don't know what the column names are going to be? Using xsl is only useful when you know exactly what data you are working with.
I found the answer. You must iterate through the dataset and make all nulls in the dataset into empty strings. Here is the code off the top of my head so there may be errors in this code, but it should give you the general idea. There is more than one way
to do this and this can be an expensive operation if your datatable is large, but you can still use the GetXml() method this way.
public static void ConvertNullsToEmptyString(Dataset dataset)
{
//This will only convert the first datatable in the dataset
for (int i = 0; i < dataset.Tables[0].Columns.Count; i++)
{
for (int j = 0; j < dataset.Tables[0].Rows.Count; j++)
{
if (dataset.Tables[0].Columns[i][j] == System.Data.DBNull.Value)
The solution from krazeeeyezkilla only works with columns of string datatype. A more general solution could be the following:
Public Shared Function GetXmlIncludingNull(ByVal dsDataset As DataSet) As String
Dim dsDatasetAux As DataSet = dsDataset.Copy()
Dim aColumnsToReplace As New List(Of DataColumn)
For Each dtTable As DataTable In dsDatasetAux.Tables
If dtTable.Rows.Count > 0 Then
For Each oColumn As DataColumn In dtTable.Columns
'check if none of the the rows has a value for the column
If dtTable.Select(String.Format("{0} is not null", oColumn.ColumnName)).Length = 0 Then
If Not oColumn.DataType Is GetType(String) Then
aColumnsToReplace.Add(oColumn)
Else
dtTable.Rows(0)(oColumn) = String.Empty
End If
End If
Next
For Each oColumn As DataColumn In aColumnsToReplace
dtTable.Columns.Remove(oColumn)
dtTable.Columns.Add(oColumn.ColumnName, GetType(String)).DefaultValue = String.Empty
'setting the value for the column in at least one row is enough for GetXML to include it
dtTable.Rows(0)(oColumn.ColumnName) = String.Empty
Next
End If
Next
dsDatasetAux.AcceptChanges()
public static string GetXmlIncludingNull(DataSet dsDataset)
{
DataSet dsDatasetAux = dsDataset.Copy();
List<DataColumn> aColumnsToReplace = new List<DataColumn>();
foreach (DataTable dtTable in dsDatasetAux.Tables) {
if (dtTable.Rows.Count > 0) {
foreach (DataColumn oColumn in dtTable.Columns) {
//check if none of the the rows has a value for the column
if (dtTable.Select(string.Format("{0} is not null", oColumn.ColumnName)).Length == 0) {
if ((!object.ReferenceEquals(oColumn.DataType, typeof(string)))) {
aColumnsToReplace.Add(oColumn);
}
else {
dtTable.Rows(0)(oColumn) = string.Empty;
}
}
}
foreach (DataColumn oColumn in aColumnsToReplace) {
dtTable.Columns.Remove(oColumn);
dtTable.Columns.Add(oColumn.ColumnName, typeof(string)).DefaultValue = string.Empty;
//setting the value for the column in at least one row is enough for GetXML to include it
dtTable.Rows(0)(oColumn.ColumnName) = string.Empty;
}
}
}
dsDatasetAux.AcceptChanges();
return dsDatasetAux.GetXml();
}
Glosh
Member
445 Points
89 Posts
DataSet.GetXML and nulls
Oct 21, 2003 02:18 PM|LINK
kuruption208...
Member
225 Points
45 Posts
Re: DataSet.GetXML and nulls
Oct 23, 2003 02:07 PM|LINK
Glosh
Member
445 Points
89 Posts
Re: DataSet.GetXML and nulls
Oct 23, 2003 02:55 PM|LINK
mighty_man
Member
470 Points
107 Posts
Re: DataSet.GetXML and nulls
Nov 13, 2003 09:23 AM|LINK
Glosh
Member
445 Points
89 Posts
Re: DataSet.GetXML and nulls
Nov 13, 2003 01:02 PM|LINK
Some Data Some more Data myCol2 is nullHope that makes some sense Cheers MarkKrazeeEyezKi...
Member
72 Points
22 Posts
Re: DataSet.GetXML and nulls
Oct 07, 2009 06:56 PM|LINK
That's all really great, but what if you don't know how many columns you are going to have? What if you don't know what the column names are going to be? Using xsl is only useful when you know exactly what data you are working with.
kavita_khand...
Star
9767 Points
1931 Posts
Re: DataSet.GetXML and nulls
Oct 08, 2009 05:01 AM|LINK
I am not sure but you have to provide the schemaInfo to the Dataset to avoid this.
I would love to change the world, but they wont give me the source code.
KrazeeEyezKi...
Member
72 Points
22 Posts
Re: DataSet.GetXML and nulls
Oct 08, 2009 04:17 PM|LINK
I found the answer. You must iterate through the dataset and make all nulls in the dataset into empty strings. Here is the code off the top of my head so there may be errors in this code, but it should give you the general idea. There is more than one way to do this and this can be an expensive operation if your datatable is large, but you can still use the GetXml() method this way.
public static void ConvertNullsToEmptyString(Dataset dataset)
{
//This will only convert the first datatable in the dataset
for (int i = 0; i < dataset.Tables[0].Columns.Count; i++)
{
for (int j = 0; j < dataset.Tables[0].Rows.Count; j++)
{
if (dataset.Tables[0].Columns[i][j] == System.Data.DBNull.Value)
dataset.Tables[0].Columns[i][j] = String.Empty
}
}
}
martin_palom...
Member
2 Points
1 Post
Re: DataSet.GetXML and nulls
Mar 04, 2010 05:36 PM|LINK
The solution from krazeeeyezkilla only works with columns of string datatype. A more general solution could be the following:
Public Shared Function GetXmlIncludingNull(ByVal dsDataset As DataSet) As String
Dim dsDatasetAux As DataSet = dsDataset.Copy()
Dim aColumnsToReplace As New List(Of DataColumn)
For Each dtTable As DataTable In dsDatasetAux.Tables
If dtTable.Rows.Count > 0 Then
For Each oColumn As DataColumn In dtTable.Columns
'check if none of the the rows has a value for the column
If dtTable.Select(String.Format("{0} is not null", oColumn.ColumnName)).Length = 0 Then
If Not oColumn.DataType Is GetType(String) Then
aColumnsToReplace.Add(oColumn)
Else
dtTable.Rows(0)(oColumn) = String.Empty
End If
End If
Next
For Each oColumn As DataColumn In aColumnsToReplace
dtTable.Columns.Remove(oColumn)
dtTable.Columns.Add(oColumn.ColumnName, GetType(String)).DefaultValue = String.Empty
'setting the value for the column in at least one row is enough for GetXML to include it
dtTable.Rows(0)(oColumn.ColumnName) = String.Empty
Next
End If
Next
dsDatasetAux.AcceptChanges()
Return dsDatasetAux.GetXml()
End Function
GetXML null DbNull XML
KrazeeEyezKi...
Member
72 Points
22 Posts
Re: DataSet.GetXML and nulls
Mar 04, 2010 06:16 PM|LINK
public static string GetXmlIncludingNull(DataSet dsDataset) { DataSet dsDatasetAux = dsDataset.Copy(); List<DataColumn> aColumnsToReplace = new List<DataColumn>(); foreach (DataTable dtTable in dsDatasetAux.Tables) { if (dtTable.Rows.Count > 0) { foreach (DataColumn oColumn in dtTable.Columns) { //check if none of the the rows has a value for the column if (dtTable.Select(string.Format("{0} is not null", oColumn.ColumnName)).Length == 0) { if ((!object.ReferenceEquals(oColumn.DataType, typeof(string)))) { aColumnsToReplace.Add(oColumn); } else { dtTable.Rows(0)(oColumn) = string.Empty; } } } foreach (DataColumn oColumn in aColumnsToReplace) { dtTable.Columns.Remove(oColumn); dtTable.Columns.Add(oColumn.ColumnName, typeof(string)).DefaultValue = string.Empty; //setting the value for the column in at least one row is enough for GetXML to include it dtTable.Rows(0)(oColumn.ColumnName) = string.Empty; } } } dsDatasetAux.AcceptChanges(); return dsDatasetAux.GetXml(); }