Xml reader not reading data into dataset

  • Thread starter Thread starter Bruno van Dooren
  • Start date Start date
B

Bruno van Dooren

hi,

i have written a program that reads data from a sql server 2000 using an
XmlReader.

when i want to stream that data into a dataset, it only gets the Scheme, not
the data.
looking in the MSDN documentation, i thought that both the data and scheme
should be
loaded into the dataset.

what am i doing wrong?

System.Xml.XmlReader myReader;
SqlCommand myCommand =
new SqlCommand("SELECT * FROM t_Data FOR XML AUTO, XMLDATA, ELEMENTS",
sqlConnTest);
sqlConnTest.Open();
myReader = myCommand.ExecuteXmlReader();
dsTest.ReadXml(myReader);
myReader.Close();
sqlConnTest.Close();

kind regards,
Bruno.
 
Some questions:

1. Does the same problem occur when using the SQL statement as below?

SELECT * FROM t_Data FOR XML AUTO, XMLDATA

2. Run the SQL statement in Query Analyze, does it works?

3. Does the same problem occur when using a different database?

4. How did you check whether or not the dataset has only schema?

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Reply-To: "Bruno van Dooren" <[email protected]>
| From: "Bruno van Dooren" <[email protected]>
| Subject: Xml reader not reading data into dataset
| Date: Fri, 26 Sep 2003 20:27:06 +0200
| Lines: 29
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: d5153d3a6.kabel.telenet.be 81.83.211.166
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62233
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| hi,
|
| i have written a program that reads data from a sql server 2000 using an
| XmlReader.
|
| when i want to stream that data into a dataset, it only gets the Scheme,
not
| the data.
| looking in the MSDN documentation, i thought that both the data and scheme
| should be
| loaded into the dataset.
|
| what am i doing wrong?
|
| System.Xml.XmlReader myReader;
| SqlCommand myCommand =
| new SqlCommand("SELECT * FROM t_Data FOR XML AUTO, XMLDATA, ELEMENTS",
| sqlConnTest);
| sqlConnTest.Open();
| myReader = myCommand.ExecuteXmlReader();
| dsTest.ReadXml(myReader);
| myReader.Close();
| sqlConnTest.Close();
|
| kind regards,
| Bruno.
|
|
|
|
|
 
1. Does the same problem occur when using the SQL statement as below?
SELECT * FROM t_Data FOR XML AUTO, XMLDATA
Click to expand...
yes. the XML looks a little different, but the dataset remains empty.

2. Run the SQL statement in Query Analyze, does it works?
Click to expand...
it returns
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
content="empty" model="closed"><AttributeType name="data_name"
dt:type="string"/><AttributeType name="value1" dt:typ

it seems not to return all data when executing your query in the query
analyser. this is probably a query analyzer issue.
if i try the following in code:
System.Xml.XmlReader myReader;

SqlCommand myCommand =

new SqlCommand("SELECT * FROM t_Data FOR XML AUTO, XMLDATA",

sqlConnTest);

sqlConnTest.Open();

myReader = myCommand.ExecuteXmlReader();


while(myReader.Read())

{

txtXml.AppendText(myReader.ReadOuterXml());

}

myReader.Close();

the textbox contains:
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
content="empty" model="closed"><AttributeType name="data_name"
dt:type="string" /><AttributeType name="value1" dt:type="r8"
/><AttributeType name="value2" dt:type="r8" /><AttributeType name="deleted"
dt:type="boolean" /><attribute type="data_name" /><attribute type="value1"
/><attribute type="value2" /><attribute type="deleted"
/></ElementType></Schema><t_Data xmlns="x-schema:#Schema1" data_name="test2"
value1="2.000000000000000e+000" value2="3.000000000000000e+000" deleted="0"
/>

even though the table does contain 3 records.
if i try the original query:

the textbox contains
<Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
content="eltOnly" model="closed" order="many"><element type="data_name"
/><element type="value1" /><element type="value2" /><element type="deleted"
/></ElementType><ElementType name="data_name" content="textOnly"
model="closed" dt:type="string" /><ElementType name="value1"
content="textOnly" model="closed" dt:type="r8" /><ElementType name="value2"
content="textOnly" model="closed" dt:type="r8" /><ElementType name="deleted"
content="textOnly" model="closed" dt:type="boolean" /></Schema><data_name
xmlns="x-schema:#Schema1">test</data_name><value2
xmlns="x-schema:#Schema1">2.000000000000000e+000</value2><data_name
xmlns="x-schema:#Schema1">test2</data_name><value2
xmlns="x-schema:#Schema1">3.000000000000000e+000</value2><data_name
xmlns="x-schema:#Schema1">test3</data_name><value2
xmlns="x-schema:#Schema1">4.000000000000000e+000</value2>

the values for column 'value1' are nowhere to be found.
3. Does the same problem occur when using a different database?
Click to expand...
i will try to test it tomorrow. i have to leave now.
4. How did you check whether or not the dataset has only schema?
Click to expand...
what do you mean by this question? do want to knwo if there is data in the
database? i think i misunderstand your question

would it be of any help if i send you my code and a script of my database?

kind regards,
Bruno.
 
I found the text size option. the complete result set is:

<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
content="empty" model="closed"><AttributeType name="data_name"
dt:type="string"/><AttributeType name="value1" dt:type="r8"/><AttributeType
name="value2" dt:type="r8"/><AttributeType name="deleted"
dt:type="boolean"/><attribute type="data_name"/><attribute
type="value1"/><attribute type="value2"/><attribute
type="deleted"/></ElementType></Schema><t_Data xmlns="x-schema:#Schema2"
data_name="test" value1="1.000000000000000e+000"
value2="2.000000000000000e+000" deleted="0"/><t_Data
xmlns="x-schema:#Schema2" data_name="test2" value1="2.000000000000000e+000"
value2="3.000000000000000e+000" deleted="0"/><t_Data
xmlns="x-schema:#Schema2" data_name="test3" value1="3.000000000000000e+000"
value2="4.000000000000000e+000" deleted="0"/>

it seems that i do not get the full XML result or something like that?
 
If the same problem still persists, you may simplify the code and script
with detailed reproducing steps, so that we are able to test this problem
on my side.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Reply-To: "Bruno van Dooren" <[email protected]>
| From: "Bruno van Dooren" <[email protected]>
| References: <[email protected]>
<[email protected]>
| Subject: Re: Xml reader not reading data into dataset
| Date: Tue, 30 Sep 2003 18:26:29 +0200
| Lines: 85
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: d5153d3a6.kabel.telenet.be 81.83.211.166
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62495
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| > 1. Does the same problem occur when using the SQL statement as below?
| > SELECT * FROM t_Data FOR XML AUTO, XMLDATA
| yes. the XML looks a little different, but the dataset remains empty.
|
|
| > 2. Run the SQL statement in Query Analyze, does it works?
| it returns
| <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
| xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
| content="empty" model="closed"><AttributeType name="data_name"
| dt:type="string"/><AttributeType name="value1" dt:typ
|
| it seems not to return all data when executing your query in the query
| analyser. this is probably a query analyzer issue.
| if i try the following in code:
| System.Xml.XmlReader myReader;
|
| SqlCommand myCommand =
|
| new SqlCommand("SELECT * FROM t_Data FOR XML AUTO, XMLDATA",
|
| sqlConnTest);
|
| sqlConnTest.Open();
|
| myReader = myCommand.ExecuteXmlReader();
|
|
| while(myReader.Read())
|
| {
|
| txtXml.AppendText(myReader.ReadOuterXml());
|
| }
|
| myReader.Close();
|
| the textbox contains:
| <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
| xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
| content="empty" model="closed"><AttributeType name="data_name"
| dt:type="string" /><AttributeType name="value1" dt:type="r8"
| /><AttributeType name="value2" dt:type="r8" /><AttributeType
name="deleted"
| dt:type="boolean" /><attribute type="data_name" /><attribute type="value1"
| /><attribute type="value2" /><attribute type="deleted"
| /></ElementType></Schema><t_Data xmlns="x-schema:#Schema1"
data_name="test2"
| value1="2.000000000000000e+000" value2="3.000000000000000e+000"
deleted="0"
| />
|
| even though the table does contain 3 records.
| if i try the original query:
|
| the textbox contains
| <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data"
| xmlns:dt="urn:schemas-microsoft-com:datatypes"><ElementType name="t_Data"
| content="eltOnly" model="closed" order="many"><element type="data_name"
| /><element type="value1" /><element type="value2" /><element
type="deleted"
| /></ElementType><ElementType name="data_name" content="textOnly"
| model="closed" dt:type="string" /><ElementType name="value1"
| content="textOnly" model="closed" dt:type="r8" /><ElementType
name="value2"
| content="textOnly" model="closed" dt:type="r8" /><ElementType
name="deleted"
| content="textOnly" model="closed" dt:type="boolean" /></Schema><data_name
| xmlns="x-schema:#Schema1">test</data_name><value2
| xmlns="x-schema:#Schema1">2.000000000000000e+000</value2><data_name
| xmlns="x-schema:#Schema1">test2</data_name><value2
| xmlns="x-schema:#Schema1">3.000000000000000e+000</value2><data_name
| xmlns="x-schema:#Schema1">test3</data_name><value2
| xmlns="x-schema:#Schema1">4.000000000000000e+000</value2>
|
| the values for column 'value1' are nowhere to be found.
|
| > 3. Does the same problem occur when using a different database?
| i will try to test it tomorrow. i have to leave now.
|
| > 4. How did you check whether or not the dataset has only schema?
| what do you mean by this question? do want to knwo if there is data in the
| database? i think i misunderstand your question
|
| would it be of any help if i send you my code and a script of my database?
|
| kind regards,
| Bruno.
|
|
|
 
Hi Bruno,

I have test the sample on my side and reproduced the same result. After
further research, I found that it is caused by the parameter in ReadXml
function.

Change
dsTest.ReadXml(myReader,XmlReadMode.Auto)

To
dsTest.ReadXml(myReader,XmlReadMode.Fragment)

It works fine on my side.

Sincerely,

Kevin
Microsoft Support

This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security

--------------------
| Reply-To: "Bruno van Dooren" <[email protected]>
| From: "Bruno van Dooren" <[email protected]>
| References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
| Subject: Re: Xml reader not reading data into dataset
| Date: Fri, 3 Oct 2003 20:53:27 +0200
| Lines: 513
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.dotnet.framework.adonet
| NNTP-Posting-Host: d5153d3a6.kabel.telenet.be 81.83.211.166
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:62793
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| Hello Kevin,
|
| I have attached my simplified solution, together with the dsTest.sql
script
| that contains the layout of my test database.
|
| the database script contains all objects, but no object permissions, users
| or logins. i think that this makes it easier for you to create the
database
| on your system.
|
| the project should compile without any modification, but i think that you
| need to modify the SQL connection strings (computer name, login and
| password) to match you local settings.
|
| when you press the button 'get XML' it reads the sql etxt into a textbox,
| and then executes the query again to try to read it directly in a dataset
| that is bound to a datagrid.
|
| the data i have in t_data is
| data_name value1 value2 deleted
| test 1.0 2.0 0
| test2 2.0 3.0 0
| test3 3.0 4.0 0
|
| kind regards,
| Bruno.
|
|
| | > If the same problem still persists, you may simplify the code and script
| > with detailed reproducing steps, so that we are able to test this
problem
| > on my side.
| >
| > Sincerely,
| >
| > Kevin
| > Microsoft Support
| >
|
|
|
 
Back
Top