How to cast in an xml based dataset SELECT method?

  • Thread starter Thread starter MurdockSE
  • Start date Start date
M

MurdockSE

Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
 
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off using
a DataView and setting the RowFilter property.
 
Thank you very much. I will attempt what you mention in #3. I think
that is a better method. Also, in #2, those are single left ticks, not
single quotes (the mark under the tilde on the keyboard as opposed to
the quote under the double quotes near ENTER) I use them as many of the
fields I use have whitespace in their column names. I just had not
removed them from this example using a name that does not have
whitespace in the column name.

Regards,

Peter

Dave said:
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off using
a DataView and setting the RowFilter property.

--
Dave Sexton

MurdockSE said:
Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
Click to expand...
Click to expand...
 
Hi Peter,

Ahh yes. #2 should be disregarded ;)

--
Dave Sexton

MurdockSE said:
Thank you very much. I will attempt what you mention in #3. I think
that is a better method. Also, in #2, those are single left ticks, not
single quotes (the mark under the tilde on the keyboard as opposed to
the quote under the double quotes near ENTER) I use them as many of the
fields I use have whitespace in their column names. I just had not
removed them from this example using a name that does not have
whitespace in the column name.

Regards,

Peter

Dave said:
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name
and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off
using
a DataView and setting the RowFilter property.

--
Dave Sexton

MurdockSE said:
Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
Click to expand...
Click to expand...
Click to expand...
 
Ok. I think I know the root of my problem.

How do I force the strong-typed dataset using an existing schema? For
example, here is the top portion of the XML file I am bringing into the
first dataset:

<?xml version="1.0" encoding="UTF-8" ?>
<IPDRDoc xmlns="http://www.ipdr.org/namespaces/ipdr"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IPDRRecorderInfo
="qca.nortelnetworks.com"
xsi:schemaLocation="http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd"
version="3.1">
<IPDR>
<StartTime>1970-01-01T00:00:00Z</StartTime>
<EndTime>1970-01-01T00:00:00Z</EndTime>
<timeZoneOffset>0</timeZoneOffset>
<callCompletionCode>CC</callCompletionCode>
<originalDestinationId></originalDestinationId>
<hostName>GWC22@WSCRCAAH00T</hostName>
<subscriberId>STS/2/0/3/VT15/3/4/4/12@HNLL0800</subscriberId>
<uniqueCallId>5360b453206046f6b626</uniqueCallId>
<ipAddress>199.173.87.2</ipAddress>
<portNumber>2944</portNumber>
<seqNum>4200064</seqNum>
<averagePacketLatency>7</averagePacketLatency>
<inboundByteCount>231040</inboundByteCount>
<outboundByteCount>230880</outboundByteCount>
<inboundPacketCount>1444</inboundPacketCount>
<outboundPacketCount>1443</outboundPacketCount>
<packetLossPercentage>0.0</packetLossPercentage>
<packetDelayVariation>0</packetDelayVariation>
</IPDR>

This is a call record on a voip server. (in case you'd like to know). I
would have thought that by the very nature of XML formats, C# would
check the schema and appropriately set the datatypes in the first
dataset. Then I wouldn't have this casting issue. If you browse out the
IPDR format -

http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd

You'll see that strong typing should already occur. Is there something
I should be adding to my ReadXML method to enforce the schema into the
dataset?

Thanks ahead of time,

Peter



Dave said:
Hi Peter,

Ahh yes. #2 should be disregarded ;)

--
Dave Sexton

MurdockSE said:
Thank you very much. I will attempt what you mention in #3. I think
that is a better method. Also, in #2, those are single left ticks, not
single quotes (the mark under the tilde on the keyboard as opposed to
the quote under the double quotes near ENTER) I use them as many of the
fields I use have whitespace in their column names. I just had not
removed them from this example using a name that does not have
whitespace in the column name.

Regards,

Peter

Dave said:
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name
and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off
using
a DataView and setting the RowFilter property.

--
Dave Sexton

Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
Click to expand...
Click to expand...
Click to expand...
 
Odd thing, I try to force System.Data.XmlReadMode.ReadSchema in the
ReadXML, and the first dataset throws an error that ds.Tables[0] does
not exist. (.NET is making my grey early). urrgh.

Peter
Ok. I think I know the root of my problem.

How do I force the strong-typed dataset using an existing schema? For
example, here is the top portion of the XML file I am bringing into the
first dataset:

<?xml version="1.0" encoding="UTF-8" ?>
<IPDRDoc xmlns="http://www.ipdr.org/namespaces/ipdr"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IPDRRecorderInfo
="qca.nortelnetworks.com"
xsi:schemaLocation="http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd"
version="3.1">
<IPDR>
<StartTime>1970-01-01T00:00:00Z</StartTime>
<EndTime>1970-01-01T00:00:00Z</EndTime>
<timeZoneOffset>0</timeZoneOffset>
<callCompletionCode>CC</callCompletionCode>
<originalDestinationId></originalDestinationId>
<hostName>GWC22@WSCRCAAH00T</hostName>
<subscriberId>STS/2/0/3/VT15/3/4/4/12@HNLL0800</subscriberId>
<uniqueCallId>5360b453206046f6b626</uniqueCallId>
<ipAddress>199.173.87.2</ipAddress>
<portNumber>2944</portNumber>
<seqNum>4200064</seqNum>
<averagePacketLatency>7</averagePacketLatency>
<inboundByteCount>231040</inboundByteCount>
<outboundByteCount>230880</outboundByteCount>
<inboundPacketCount>1444</inboundPacketCount>
<outboundPacketCount>1443</outboundPacketCount>
<packetLossPercentage>0.0</packetLossPercentage>
<packetDelayVariation>0</packetDelayVariation>
</IPDR>

This is a call record on a voip server. (in case you'd like to know). I
would have thought that by the very nature of XML formats, C# would
check the schema and appropriately set the datatypes in the first
dataset. Then I wouldn't have this casting issue. If you browse out the
IPDR format -

http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd

You'll see that strong typing should already occur. Is there something
I should be adding to my ReadXML method to enforce the schema into the
dataset?

Thanks ahead of time,

Peter



Dave said:
Hi Peter,

Ahh yes. #2 should be disregarded ;)

--
Dave Sexton

MurdockSE said:
Thank you very much. I will attempt what you mention in #3. I think
that is a better method. Also, in #2, those are single left ticks, not
single quotes (the mark under the tilde on the keyboard as opposed to
the quote under the double quotes near ENTER) I use them as many of the
fields I use have whitespace in their column names. I just had not
removed them from this example using a name that does not have
whitespace in the column name.

Regards,

Peter

Dave Sexton wrote:
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name
and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off
using
a DataView and setting the RowFilter property.

--
Dave Sexton

Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
Click to expand...
Click to expand...
Click to expand...
 
I've used an inferschema readmode, and it's slightly working. I am
having issues iterating the resulting table ; but I'll figure that part
out and report again.

Peter
Odd thing, I try to force System.Data.XmlReadMode.ReadSchema in the
ReadXML, and the first dataset throws an error that ds.Tables[0] does
not exist. (.NET is making my grey early). urrgh.

Peter
Ok. I think I know the root of my problem.

How do I force the strong-typed dataset using an existing schema? For
example, here is the top portion of the XML file I am bringing into the
first dataset:

<?xml version="1.0" encoding="UTF-8" ?>
<IPDRDoc xmlns="http://www.ipdr.org/namespaces/ipdr"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" IPDRRecorderInfo
="qca.nortelnetworks.com"
xsi:schemaLocation="http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd"
version="3.1">
<IPDR>
<StartTime>1970-01-01T00:00:00Z</StartTime>
<EndTime>1970-01-01T00:00:00Z</EndTime>
<timeZoneOffset>0</timeZoneOffset>
<callCompletionCode>CC</callCompletionCode>
<originalDestinationId></originalDestinationId>
<hostName>GWC22@WSCRCAAH00T</hostName>
<subscriberId>STS/2/0/3/VT15/3/4/4/12@HNLL0800</subscriberId>
<uniqueCallId>5360b453206046f6b626</uniqueCallId>
<ipAddress>199.173.87.2</ipAddress>
<portNumber>2944</portNumber>
<seqNum>4200064</seqNum>
<averagePacketLatency>7</averagePacketLatency>
<inboundByteCount>231040</inboundByteCount>
<outboundByteCount>230880</outboundByteCount>
<inboundPacketCount>1444</inboundPacketCount>
<outboundPacketCount>1443</outboundPacketCount>
<packetLossPercentage>0.0</packetLossPercentage>
<packetDelayVariation>0</packetDelayVariation>
</IPDR>

This is a call record on a voip server. (in case you'd like to know). I
would have thought that by the very nature of XML formats, C# would
check the schema and appropriately set the datatypes in the first
dataset. Then I wouldn't have this casting issue. If you browse out the
IPDR format -

http://www.ipdr.org/namespaces/IPDR/IPDRDoc3.1.xsd

You'll see that strong typing should already occur. Is there something
I should be adding to my ReadXML method to enforce the schema into the
dataset?

Thanks ahead of time,

Peter



Dave said:
Hi Peter,

Ahh yes. #2 should be disregarded ;)

--
Dave Sexton

Thank you very much. I will attempt what you mention in #3. I think
that is a better method. Also, in #2, those are single left ticks, not
single quotes (the mark under the tilde on the keyboard as opposed to
the quote under the double quotes near ENTER) I use them as many of the
fields I use have whitespace in their column names. I just had not
removed them from this example using a name that does not have
whitespace in the column name.

Regards,

Peter

Dave Sexton wrote:
Hi Peter,

1. Create a strong-Typed DataSet and define the schema as you want it or add the Typed columns to your DataSet at runtime before
reading the data.
2. 'myStringField' should not be quoted in the string you are passing to the Select method since it's referring to a column name
and
not a string literal: Select("myStringField > 19.99")
3. Instead of cloning your DataSet, calling Select, looping through the results and filling the clone, you might be better off
using
a DataView and setting the RowFilter property.

--
Dave Sexton

Greetings.

My Situation:

// I have an .xml file that I am reading into a dataset in the
following code -

DataSet ds = new DataSet("MyDataset");
ds.ReadXml(@"c:\data\"+cmyxmlfilename);

// I am then attempting to obtain a subset of that dataset to filter
the data as follows -

DataSet subSet = ds.Clone();

DataRow[] copyRows = ds.Tables[1].Select("`myStringField` > 19.99");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);

}

My Question:
When you read an XML file in this manner, how do you cast the columns
to another type for a SELECT filter expression?

For example, myStringField contains what is actually a "double", but in
string format, as the XMLRead method doesn't allow you to type the
resulting fields. How do I cast in a SELECT filter expression on a
dataset created from XML using ReadXML? I've attempted to cast the type
of the datacolumn in dataset "ds", but it errors out stating I cannot
change it after there is data in the dataset.... well, chicken or egg
flambe? How do I cast the column type prior to the SELECT filter
expression? Or is there a way in the Select method to call upon a cast?
I could not figure out a method that would work.

Thanks ahead of time,

Peter Robbins
"Oh, .NET is easy....... if you know how to do it".
Click to expand...
Click to expand...
Click to expand...
 
Ok. Inferschema worked. Here is the main portion, and the function it
calls to write to file any span that experiences a packetloss% over 19%
--

DataSet ds = new DataSet("CS2KCallRecords");
ds.ReadXml(@"c:\data\" + xmlfilename, XmlReadMode.InferTypedSchema);

public void WriteAllBadLossToFile(DataSet dataset, StreamWriter sw)
{
DataSet subSet = dataset.Clone();

subSet.EnforceConstraints = false;

DataRow[] copyRows =
dataset.Tables[1].Select("`packetLossPercentage` > 19");

DataTable myTable = subSet.Tables[1];

foreach (DataRow copyRow in copyRows)
{
myTable.ImportRow(copyRow);
}

foreach (DataRow row in myTable.Rows)
{
foreach (Object value in row.ItemArray)
{
if (row.ItemArray.Length > 15)
{
sw.WriteLine(row.ItemArray[6].ToString() + "
experienced Packet Loss % of: " + row.ItemArray[16].ToString() + "%");
}
}
}
}


Thank you for pointing me in the right direction , or at least
listening while I go nuts figuring out .NET.... :) I'm quoted as
saying ".NET is easy, if you know what you are doing." Unfortunately, I
prove this daily!!! I've never run across a more convoluted yet
powerful construct as ADO.NET :)
 
Lol. I'm glad you figured it out. I was out eating breakfast. :)

You are correct that you must tell the DataSet to use the schema. However, in this case you are still not creating a strong-Typed
DataSet, but your solution might be fine for your needs.

To create a strong-Typed DataSet you can simply add the .xsd to Visual Studio. That's all you have to do, but there is a potential
problem with strong-Typing the schema. The issue is that schema changes are not automatically reflected in your program. Schema
changes would probably be a breaking change for your application either way so strong-typing might still be the better solution for
you.
 
Ok. So, I've added a copy of the referenced .xsd as an 'existing item'
to the project as IPDRDoc.xsd. If I remove the
XmlReadMode.InferTypedSchema from the ReadXML line, the solution fails
out again when attempting to select any packetLossPercentage above
integer 19; stating it cannot compare a string to integer, blah blah.

Is there anything more then adding the file to the solution that I have
to do? I'm sure there is something blatently obvious that is just not
so obvious to me. :)

And I'll leave you alone after this :)
 
Hi,

I could not find the definition of packetLossPercentage in the .xsd you referenced in a previous post. You might need to find an
updated schema.

If you don't have an updated schema, open your .xsd in VS.NET and select the packetLossPercentage column if it exists. In the
property grid manually change the DataType property to System.Double. If it doesn't exist, as I would expect, add it. The default
type will be System.String. Now the error makes perfect sense. After all, the column was added at runtime and you got an error
about invalid comparison between a string and an integer.
 
Agreed. I may just create my own from scratch. I have a case open with
the vendor as the referenced xsd does not match the format of the ipdrs
in the file. (also in the real one that I download and ungzip, the URL
is wrong too -- I had corrected it before posting the example). As it
stands, the inferedschema xmlreadmode is working as desired for this
simple project. As this is not a commercial product - and hence, likely
no additional parsing will be necessary, It will suffice for now. I
will correct it when I can get a more valid schema to work from.

Thanks for all your help ; I'll let you all know how this turns out.

Peter Robbins
 
Back
Top