DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
..WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
Mike
You have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike
|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
selectconvert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike
|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegrou ps.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>

DataSet Xml DateTime incompatible with Sql 2005

All I'm trying to do is simply write out a DataSet in .net 2.0 with
.WriteXml(), then read it into sql 2005 with OPENXML.
WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
Yet it appears sql 2005 doesn't support that format. Is that possible?
Running the below code gives:
"Conversion failed when converting datetime from character string."
declare @.Xml varchar(max)
declare @.iRet int
declare @.hDoc int
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>
'
exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
select SaveDate
from openxml(@.hDoc, N'/ROOT/Favorite', 2)
with Favorite
thanks-
MikeYou have a datetime value with a timezone which is not recognized with
OpenXML.
Try one of the following instead:
1. do not generate datetime values with timezones.
2. Use the nodes method (needs to explicitly code the table shape):
declare @.Xml xml
set @.Xml = '<ROOT>
<Favorite>
<Directory>\Astronomy\Aurora\</Directory>
<Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
<Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
<SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
</Favorite>
</ROOT>'
select R.Fav.value('xs:dateTime(SaveDate[1])', 'datetime') as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Note that you need to first cast it to xs:dateTime to normalize the value to
Z time and then cast it to datetime which will drop the timezone
altogether...
Season's Greetings
Michael
"Mike" <nospam@.dontemailme.com> wrote in message
news:esL2%23A4BGHA.3936@.TK2MSFTNGP12.phx.gbl...
> All I'm trying to do is simply write out a DataSet in .net 2.0 with
> .WriteXml(), then read it into sql 2005 with OPENXML.
> WriteXml() produces dates in the format "2004-07-14T23:50:13-07:00"
> Yet it appears sql 2005 doesn't support that format. Is that possible?
> Running the below code gives:
> "Conversion failed when converting datetime from character string."
> --
> declare @.Xml varchar(max)
> declare @.iRet int
> declare @.hDoc int
> set @.Xml = '<ROOT>
> <Favorite>
> <Directory>\Astronomy\Aurora\</Directory>
> <Name>3-day Estimated Planetary Kp-index Monitor.url</Name>
> <Url>http://sec.noaa.gov/rt_plots/kp_3d.html</Url>
> <SaveDate>2004-07-14T23:50:13-07:00</SaveDate>
> </Favorite>
> </ROOT>
> '
> exec @.iRet = sp_xml_preparedocument @.hDoc OUTPUT, @.Xml
> select SaveDate
> from openxml(@.hDoc, N'/ROOT/Favorite', 2)
> with Favorite
>
> thanks-
> Mike|||Michael-
That still gave me a 'Conversion failed...' error but did get me on the
right track. Extracting as a string first then converting did the
trick.
select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
'char(20)'),127) as SaveDate
from @.Xml.nodes('/ROOT/Favorite') R(Fav)
Thanks much for the quick response - it really helped.
Mike|||Hmm. What version of SQL Server 2005 are you currently running?
This should work automatically without you having to do the string/datetime
yourself in the RTM version...
Best regards
Michael
<mhardy@.gmail.com> wrote in message
news:1135818951.156103.3160@.g49g2000cwa.googlegroups.com...
> Michael-
> That still gave me a 'Conversion failed...' error but did get me on the
> right track. Extracting as a string first then converting did the
> trick.
> select convert(datetime, R.Fav.value('xs:dateTime(SaveDate[1])',
> 'char(20)'),127) as SaveDate
> from @.Xml.nodes('/ROOT/Favorite') R(Fav)
> Thanks much for the quick response - it really helped.
> Mike
>sql

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

DataSet Xml DateTime incompatible with Sql 2000

This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></root>
>
|||Thanks for the quick response. It was exactly what I needed to know. I have
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since this
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:

> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first, if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>
|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...[vbcol=seagreen]
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
|||I was confused and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, but
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:

> Here is a sample. Note that you can inline the TSQL function into the select
> clause directly. You may also want to add some more complex logic to adjust
> the date time based on the timezone if you expect more than one timezone to
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
GabrielHello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're look
ing
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're lo
oking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>sql

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
Gabriel
Hello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're looking
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're looking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>

DataSet to SQL script?

Hi,Does anyone have a way to generate a SQL 2000 database generation script from a DataSet? I'm looking to load an XML file into a DataSet and use it to create the db design.Matt.

You can load elements/attributes into fields of database tables, which contains information used to create a database. Then you can query out the information, and use it in dynamic sql statement. For example if we want to create such a database using DataFileName, LogFileName, DatabaseName, Size, FileGrowth frommyTable:

DECLARE @.DFN sysname,@.LFN sysname, @.DBN sysname
DECLARE @.Size varchar(4),@.FG varchar(4)
SELECT @.DFN=DataFileName, @.LFN=LogFileName, @.DBN=DatabaseName,
@.Size=Size, @.FG=FileGrowth
FROMmyTable

--select @.DFN='c:\saledat.mdf',@.LFN='c:\salelog.ldf',@.DBN='Sales',
--@.Size='10MB',@.FG='5MB'
EXEC('CREATE DATABASE'+@.DBN+'
ON
( NAME = Sales_dat,
FILENAME ='''+@.DFN+''',
SIZE ='+@.Size+',
MAXSIZE = 50,
FILEGROWTH ='+@.FG+' )
LOG ON
( NAME = Sales_log,
FILENAME ='''+@.LFN+''',
SIZE ='+@.Size+',
MAXSIZE = 25,
FILEGROWTH ='+@.FG+' )')

Note: the aboving create database statement recieves @.Size and @.FG as varchar datatype.

|||Thanks, but I was under the impression that the BulkInsert3 method (SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute() found in xblkld3.dll) would take an xml schema and xml file and actually create the tables, within a specified database, if they were not already present.Do you know if this is actually possible?Matt.|||Actually, to clarify, I would like an automated version of the article mentioned previously (http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/) in order to generate a db (well, the tables) script.This comes from seeing Visual Studio open an XML file, click on XML->Create Schema and View Designer. This shows what I would derive a db table design from.However, currently I am loading the XML into a DataSet and generating the xml schema from that. I may have to simply generate the database tables from looking at the DataSet and then use the bulk insert library.Obviously, my intention here is to do as much of the work with automation as possible (as any good/lazy coder would) but it just looks like some crowbarring is needed.Unless anyone knows how to generate a databases tables from the XML/XML schema or DataSet directly?Matt.

Dataset or SqlCommand or SQLXML

I am in a dilemma(rather "trilemma"!!!).
I have a Microsoft SQL 2000 Server and I want to retrieve data from the
server in XML format. Can do it without even utilizing the special XML
capabilites of SQL 2000 server.
I mean I can very well use:
1. Store the data retieved from the DB in a dataset and use WriteXML()
function.
2. Use SQLExecuteReader() function of SqlCommand class
Is their some advantage/need(may be performance issues etc.) to use SqlXml
managed classes for *only* conversion of data retrieved from SQL server to
XML format?
Also in case we dont have SQL 2000 server (suppose an earlier version or a
non-Microsoft DB) then too the aforelisted 2 methods for accessing XML data
would work...are'nt they?
Suggestions/comments invited.
-Aayush
What you say is partially correct - if you simply want to convert the
relational data to XML, you can do it using the WriteXml method of a
DataSet.
As regards your second option, I assume you mean the ExecuteXmlReader method
of SqlCommand - and this will only work with a FOR XML query against SQL
Server 2000 - so you couldn't use this approach with other data sources.
See http://www.microsoft.com/mspress/boo...#SampleChapter
for a comparison of using "standard" ADO.NET vs SqlXml classes. There are
quite a few advantages to using the SqlXml classes if your data is in SQL
Server 2000 - I guess it depends whether or not you need the additional
flexibility and functionality they offer.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Aayush Puri" <aayush@.nospam.com> wrote in message
news:%23VXfr16TEHA.2716@.tk2msftngp13.phx.gbl...
I am in a dilemma(rather "trilemma"!!!).
I have a Microsoft SQL 2000 Server and I want to retrieve data from the
server in XML format. Can do it without even utilizing the special XML
capabilites of SQL 2000 server.
I mean I can very well use:
1. Store the data retieved from the DB in a dataset and use WriteXML()
function.
2. Use SQLExecuteReader() function of SqlCommand class
Is their some advantage/need(may be performance issues etc.) to use SqlXml
managed classes for *only* conversion of data retrieved from SQL server to
XML format?
Also in case we dont have SQL 2000 server (suppose an earlier version or a
non-Microsoft DB) then too the aforelisted 2 methods for accessing XML data
would work...are'nt they?
Suggestions/comments invited.
-Aayush

Dataset Manipulation

Have an XML file that i load in to a Dataset. works fine, it builds its own scheme perfectly.

I loop through that data to load a check list which also works wonderfully.

two questions based on that.

1) can i add a column after the fact? I want to basically update the the info in the dataset to store if the record was checked in the check list. if not, i can manipulate one of the already defined columns, but i would rather not.

2) what is the best way to update data with in the dataset? Never really done anything but pull data from one. how do i locate the correct row to update ("select name from tbl where name = " + checklist.items[index].tostring(); update row)

sorry for the fairly basic question. i appreciate the help.

Justin

re #1: You can add columns to dataset tables at any time.

DataColumn _dc =newDataColumn("newcolumnname");

mydataset.Tables[0].Columns.Add(_dc);

re #2: You are asking more than just updating. You are asking how to find the row to update as well.

Create a DataView object to pass in QueryStatements to find the records you want.

Then edit the field value, something such as:

mydataset.Tables[0].Rows[4][

"newcolumnname"] ="newvalue";

mydataset.AcceptChanges();

|||

great, thanks. not really shore how i missed the column.add, i was even looking for that...

I found something else i might try for the locate and update. because it reads from an XML with no schema it doesnt create a PK. I was going to set a PK (MyDataTable.PrimaryKey = PKColumn) and then use the Find method to locate the row i want (myDataTable.Rows.Find(objValue))

not sure how it will workout, going to give it a try. if not i can do as you suggested.

I appreciate the help.

Thanks

Justin

dataset and xml problem

Hello all,
I'm tring to coordinate between a database and a my application throw a
dataset object and a group of dataAdapters, one for each table. to
display data, I use the xml driven from the method DataSet.WriteXml().
to store data I want to receive xml based on the dataset structure and
let the data set store and update everything through the method
DataSet.ReadXML(). this I have difficultes to perform and I cant find
any good examples.
I'd appreciate your help.First, I think you need to reexplain what youre trying to do.
I've read it 3 times, and still don't know exactly.
..
Second...If you're interested in passing dataset xml to a stored
procedure...
go here:
http://www.sqlservercentral.com/col...lem.as
p
Its not ~exactly what you're looking for.. but will give you a place to
start.
Create an xsd/dataset.
It could look something like this:
<ParametersDS>
<Customer>
<CustomerID>CENTC</CustomerID>
</Customer>
<Customer>
<CustomerID>GROSR</CustomerID>
</Customer>
</ParametersDS>
...
If that's not what you're looking for... (after you go thru the URL I
provide), then repost what you're trying to do, and explain it a little
slower and greater detail.
<taleran58@.gmail.com> wrote in message
news:1150107036.031001.242910@.j55g2000cwa.googlegroups.com...
> Hello all,
> I'm tring to coordinate between a database and a my application throw a
> dataset object and a group of dataAdapters, one for each table. to
> display data, I use the xml driven from the method DataSet.WriteXml().
> to store data I want to receive xml based on the dataset structure and
> let the data set store and update everything through the method
> DataSet.ReadXML(). this I have difficultes to perform and I cant find
> any good examples.
> I'd appreciate your help.
>

Datagram message

My app was working fine (no change in the last couple of years) until
recently.
Here is an example of my XML -
<root
xmlns:updg="urn:schemas-microsoft-com:xml-updategram"><updg:sync><updg:before><ProbePart
VFPID="28724"/></updg:before><updg:after><ProbePart
ReconSell="5520"/></updg:after></updg:sync></root>
which results in the Response -
<H3>ERROR: 400.100 Bad Request</H3><b>HResult:</b>
0x80004005<br><b>Source:</b> Microsoft SQL isapi
extension<br><b>Description:</b> Query not specified<br>
Not sure where to look for resolution - any pointers would be appreciated.
Could you please provide some more information? Like what your setup is and
a simple complete repro?
Thanks
Michael
"howard" <howard@.discussions.microsoft.com> wrote in message
news:1B14174A-7CC0-4B78-BB8E-15EC906834BE@.microsoft.com...
> My app was working fine (no change in the last couple of years) until
> recently.
> Here is an example of my XML -
> <root
> xmlns:updg="urn:schemas-microsoft-com:xml-updategram"><updg:sync><updg:before><ProbePart
> VFPID="28724"/></updg:before><updg:after><ProbePart
> ReconSell="5520"/></updg:after></updg:sync></root>
> which results in the Response -
> <H3>ERROR: 400.100 Bad Request</H3><b>HResult:</b>
> 0x80004005<br><b>Source:</b> Microsoft SQL isapi
> extension<br><b>Description:</b> Query not specified<br>
> Not sure where to look for resolution - any pointers would be appreciated.
>
>