MS SQL XML - SqlXml 3 and .NET 2 problem

Author

SqlXml 3 and .NET 2 problem

Phil Lee

2006-01-11, 9:23 am

Hi,

I have just migrated some .NET 1.1 code which uses SqlXml to .NET 2 and have
hit a problem.

The code looks like this:

SqlXmlCommand cmd = new SqlXmlCommand( "connection string" );

cmd.RootTag = "root tag";
cmd.CommandText = "xpath query";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.SchemaPath = "schema path";

MyDataSet ds = new MyDataSet() -- generated from .xsd
ds.ReadXmlSchema( "schema path" );

SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
ad.Fill(ds);

The problem is that ad.Fill() fails to fill the DataSet. Using SqlProfiler
I can see that the correct SQL is created and executed.
I assume the problem is that SqlXmlAdapter.Fill is expecting a .NET 1
DataSet but is receiving a .NET 2 DataSet. There's no exception however.

Am I correct in my diagnosis? And if so what work arounds are available -
other than staying with .NET 1?

Regards
Phil Lee

Phil Lee

2006-01-11, 11:24 am

Further to this problem.

MyDataSet is a typed DataSet based on a different .xsd from the
cmd.SchemaPath but with matching tables. This code worked in .NET 1.1.

If I just use
DataSet ds = new DataSet();
SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
ad.Fill(ds);

then the dataSet is filled correctly. However I can't then manage to copy
the data to the typed data set? Any ideas?

"Phil Lee" <phil.lee@newsgroups.nospam> wrote in message
news:umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have just migrated some .NET 1.1 code which uses SqlXml to .NET 2 and
> have hit a problem.
>
> The code looks like this:
>
> SqlXmlCommand cmd = new SqlXmlCommand( "connection string" );
>
> cmd.RootTag = "root tag";
> cmd.CommandText = "xpath query";
> cmd.CommandType = SqlXmlCommandType.XPath;
> cmd.SchemaPath = "schema path";
>
> MyDataSet ds = new MyDataSet() -- generated from .xsd
> ds.ReadXmlSchema( "schema path" );
>
> SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
> ad.Fill(ds);
>
>
> The problem is that ad.Fill() fails to fill the DataSet. Using
> SqlProfiler I can see that the correct SQL is created and executed.
> I assume the problem is that SqlXmlAdapter.Fill is expecting a .NET 1
> DataSet but is receiving a .NET 2 DataSet. There's no exception however.
>
> Am I correct in my diagnosis? And if so what work arounds are available -
> other than staying with .NET 1?
>
> Regards
> Phil Lee
>

Steven Cheng[MSFT]

2006-01-12, 3:24 am

Hi Phil,

As for the SqlXmlAdapter DataSet query, I think it is still possibly caused
by the SqlXmlCommand's returned xml stream dosn't conform to the
TypedDataSet's xml schema. You can first use SqlXmlCommand to do the
StreamQuery and save the xml data to see whether it confirm to the
typeddataset's XMLSchema or compared to the TypeDataSet's query's normal
returned xml data....

Also, one quick and simple means to merge the untyped DataSet's data to
Typed Dataset is using the DataTAble.Load method which take a IDataReader
object that we can created from anothe DataSet. So something like:

==============
DataSet ds1 = new DataSet();
SqlXmlAdapter adapter = new SqlXmlAdapter(comm);

adapter.Fill(ds1);

DataSet1 tds = new DataSet1();

tds.Categories.Load(ds1.Tables[0]. CreateDataReader());

==============

Hope helps. Thanks,

Steven Cheng
Microsoft Online Support

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

--------------------
| From: "Phil Lee" <phil.lee@newsgroups.nospam>
| References: <umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl>
| Subject: Re: SqlXml 3 and .NET 2 problem
| Date: Wed, 11 Jan 2006 17:15:07 -0000
| Lines: 49
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Response
| Message-ID: <eNl2UKtFGHA.1424@TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: host86-132-72-9.range86-132.btcentralplus.com
86.132.72.9
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26959
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Further to this problem.
|
| MyDataSet is a typed DataSet based on a different .xsd from the
| cmd.SchemaPath but with matching tables. This code worked in .NET 1.1.
|
| If I just use
| DataSet ds = new DataSet();
| SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
| ad.Fill(ds);
|
| then the dataSet is filled correctly. However I can't then manage to
copy
| the data to the typed data set? Any ideas?
|
| "Phil Lee" <phil.lee@newsgroups.nospam> wrote in message
| news:umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl...
| > Hi,
| >
| > I have just migrated some .NET 1.1 code which uses SqlXml to .NET 2 and
| > have hit a problem.
| >
| > The code looks like this:
| >
| > SqlXmlCommand cmd = new SqlXmlCommand( "connection string" );
| >
| > cmd.RootTag = "root tag";
| > cmd.CommandText = "xpath query";
| > cmd.CommandType = SqlXmlCommandType.XPath;
| > cmd.SchemaPath = "schema path";
| >
| > MyDataSet ds = new MyDataSet() -- generated from .xsd
| > ds.ReadXmlSchema( "schema path" );
| >
| > SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
| > ad.Fill(ds);
| >
| >
| > The problem is that ad.Fill() fails to fill the DataSet. Using
| > SqlProfiler I can see that the correct SQL is created and executed.
| > I assume the problem is that SqlXmlAdapter.Fill is expecting a .NET 1
| > DataSet but is receiving a .NET 2 DataSet. There's no exception
however.
| >
| > Am I correct in my diagnosis? And if so what work arounds are
available -
| > other than staying with .NET 1?
| >
| > Regards
| > Phil Lee
| >
|
|
|

Phil Lee

2006-01-12, 7:23 am

Steven,

thanks, that got me on the right path.

The schema of the data returned is correct.
However, if I tried DataTable.Load as you suggested I got an
ArgumentException: "String was not recognized as a valid Boolean.Couldn't
store <0> in Registered Column. Expected type is Boolean."
So I tried adding ds.ReadXmlSchema( "mapping schema" ) and then the
DataTable.Load worked.

Regards
Phil

"Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
news:aZQdEg0FGHA.3152@TK2MSFTNGXA02.phx.gbl...
> Hi Phil,
>
> As for the SqlXmlAdapter DataSet query, I think it is still possibly
> caused
> by the SqlXmlCommand's returned xml stream dosn't conform to the
> TypedDataSet's xml schema. You can first use SqlXmlCommand to do the
> StreamQuery and save the xml data to see whether it confirm to the
> typeddataset's XMLSchema or compared to the TypeDataSet's query's normal
> returned xml data....
>
> Also, one quick and simple means to merge the untyped DataSet's data to
> Typed Dataset is using the DataTAble.Load method which take a IDataReader
> object that we can created from anothe DataSet. So something like:
>
> ==============
> DataSet ds1 = new DataSet();
> SqlXmlAdapter adapter = new SqlXmlAdapter(comm);

> adapter.Fill(ds1);
>
> DataSet1 tds = new DataSet1();
>
> tds.Categories.Load(ds1.Tables[0]. CreateDataReader());

> ==============
>
> Hope helps. Thanks,
>
> Steven Cheng
> Microsoft Online Support
>
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>
> --------------------
> | From: "Phil Lee" <phil.lee@newsgroups.nospam>
> | References: <umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl>
> | Subject: Re: SqlXml 3 and .NET 2 problem
> | Date: Wed, 11 Jan 2006 17:15:07 -0000
> | Lines: 49
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
> | X-RFC2646: Format=Flowed; Response
> | Message-ID: <eNl2UKtFGHA.1424@TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.xml
> | NNTP-Posting-Host: host86-132-72-9.range86-132.btcentralplus.com
> 86.132.72.9
> | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
> | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26959
> | X-Tomcat-NG: microsoft.public.sqlserver.xml
> |
> | Further to this problem.
> |
> | MyDataSet is a typed DataSet based on a different .xsd from the
> | cmd.SchemaPath but with matching tables. This code worked in .NET 1.1.
> |
> | If I just use
> | DataSet ds = new DataSet();
> | SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
> | ad.Fill(ds);
> |
> | then the dataSet is filled correctly. However I can't then manage to
> copy
> | the data to the typed data set? Any ideas?
> |
> | "Phil Lee" <phil.lee@newsgroups.nospam> wrote in message
> | news:umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl...
> | > Hi,
> | >
> | > I have just migrated some .NET 1.1 code which uses SqlXml to .NET 2
> and
> | > have hit a problem.
> | >
> | > The code looks like this:
> | >
> | > SqlXmlCommand cmd = new SqlXmlCommand( "connection string" );
> | >
> | > cmd.RootTag = "root tag";
> | > cmd.CommandText = "xpath query";
> | > cmd.CommandType = SqlXmlCommandType.XPath;
> | > cmd.SchemaPath = "schema path";
> | >
> | > MyDataSet ds = new MyDataSet() -- generated from .xsd
> | > ds.ReadXmlSchema( "schema path" );
> | >
> | > SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
> | > ad.Fill(ds);
> | >
> | >
> | > The problem is that ad.Fill() fails to fill the DataSet. Using
> | > SqlProfiler I can see that the correct SQL is created and executed.
> | > I assume the problem is that SqlXmlAdapter.Fill is expecting a .NET 1
> | > DataSet but is receiving a .NET 2 DataSet. There's no exception
> however.
> | >
> | > Am I correct in my diagnosis? And if so what work arounds are
> available -
> | > other than staying with .NET 1?
> | >
> | > Regards
> | > Phil Lee
> | >
> |
> |
> |
>

Steven Cheng[MSFT]

2006-01-12, 8:24 pm

That's cool Phil,

Glad that it helps.

Regards,

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
--------------------
| From: "Phil Lee" <phil.lee@newsgroups.nospam>
| References: <umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl>
<eNl2UKtFGHA.1424@TK2MSFTNGP12.phx.gbl>
<aZQdEg0FGHA.3152@TK2MSFTNGXA02.phx.gbl>
| Subject: Re: SqlXml 3 and .NET 2 problem
| Date: Thu, 12 Jan 2006 11:17:15 -0000
| Lines: 128
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <#CaNCn2FGHA.1088@tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: host86-132-72-9.range86-132.btcentralplus.com
86.132.72.9
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26971
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Steven,
|
| thanks, that got me on the right path.
|
| The schema of the data returned is correct.
| However, if I tried DataTable.Load as you suggested I got an
| ArgumentException: "String was not recognized as a valid Boolean.Couldn't
| store <0> in Registered Column. Expected type is Boolean."
| So I tried adding ds.ReadXmlSchema( "mapping schema" ) and then the
| DataTable.Load worked.
|
| Regards
| Phil
|
| "Steven Cheng[MSFT]" <stcheng@online.microsoft.com> wrote in message
| news:aZQdEg0FGHA.3152@TK2MSFTNGXA02.phx.gbl...
| > Hi Phil,
| >
| > As for the SqlXmlAdapter DataSet query, I think it is still possibly
| > caused
| > by the SqlXmlCommand's returned xml stream dosn't conform to the
| > TypedDataSet's xml schema. You can first use SqlXmlCommand to do the
| > StreamQuery and save the xml data to see whether it confirm to the
| > typeddataset's XMLSchema or compared to the TypeDataSet's query's normal
| > returned xml data....
| >
| > Also, one quick and simple means to merge the untyped DataSet's data to
| > Typed Dataset is using the DataTAble.Load method which take a
IDataReader
| > object that we can created from anothe DataSet. So something like:
| >
| > ==============
| > DataSet ds1 = new DataSet();
| > SqlXmlAdapter adapter = new SqlXmlAdapter(comm);

| > adapter.Fill(ds1);
| >
| > DataSet1 tds = new DataSet1();
| >
| > tds.Categories.Load(ds1.Tables[0]. CreateDataReader());

| > ==============
| >
| > Hope helps. Thanks,
| >
| > Steven Cheng
| > Microsoft Online Support
| >
| > Get Secure! www.microsoft.com/security
| > (This posting is provided "AS IS", with no warranties, and confers no
| > rights.)
| >
| >
| >
| >
| > --------------------
| > | From: "Phil Lee" <phil.lee@newsgroups.nospam>
| > | References: <umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl>
| > | Subject: Re: SqlXml 3 and .NET 2 problem
| > | Date: Wed, 11 Jan 2006 17:15:07 -0000
| > | Lines: 49
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Response
| > | Message-ID: <eNl2UKtFGHA.1424@TK2MSFTNGP12.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.xml
| > | NNTP-Posting-Host: host86-132-72-9.range86-132.btcentralplus.com
| > 86.132.72.9
| > | Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| > | Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26959
| > | X-Tomcat-NG: microsoft.public.sqlserver.xml
| > |
| > | Further to this problem.
| > |
| > | MyDataSet is a typed DataSet based on a different .xsd from the
| > | cmd.SchemaPath but with matching tables. This code worked in .NET
1.1.
| > |
| > | If I just use
| > | DataSet ds = new DataSet();
| > | SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
| > | ad.Fill(ds);
| > |
| > | then the dataSet is filled correctly. However I can't then manage to
| > copy
| > | the data to the typed data set? Any ideas?
| > |
| > | "Phil Lee" <phil.lee@newsgroups.nospam> wrote in message
| > | news:umNixirFGHA.3912@TK2MSFTNGP10.phx.gbl...
| > | > Hi,
| > | >
| > | > I have just migrated some .NET 1.1 code which uses SqlXml to .NET 2
| > and
| > | > have hit a problem.
| > | >
| > | > The code looks like this:
| > | >
| > | > SqlXmlCommand cmd = new SqlXmlCommand( "connection string" );
| > | >
| > | > cmd.RootTag = "root tag";
| > | > cmd.CommandText = "xpath query";
| > | > cmd.CommandType = SqlXmlCommandType.XPath;
| > | > cmd.SchemaPath = "schema path";
| > | >
| > | > MyDataSet ds = new MyDataSet() -- generated from .xsd
| > | > ds.ReadXmlSchema( "schema path" );
| > | >
| > | > SqlXmlAdapter ad = new SqlXmlAdapter(cmd);
| > | > ad.Fill(ds);
| > | >
| > | >
| > | > The problem is that ad.Fill() fails to fill the DataSet. Using
| > | > SqlProfiler I can see that the correct SQL is created and executed.
| > | > I assume the problem is that SqlXmlAdapter.Fill is expecting a .NET
1
| > | > DataSet but is receiving a .NET 2 DataSet. There's no exception
| > however.
| > | >
| > | > Am I correct in my diagnosis? And if so what work arounds are
| > available -
| > | > other than staying with .NET 1?
| > | >
| > | > Regards
| > | > Phil Lee
| > | >
| > |
| > |
| > |
| >
|
|
|

Sponsored Links