MS SQL XML - sp_xml_preparedocument

Author

sp_xml_preparedocument

INeedADip

2006-01-11, 3:23 am

I have seen this problem posted all over, but have never ran across a
solution....
I am serializing my dataset and they look like this:

<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLoc
ale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="List_101">
<xs:complexType>
<xs:attribute name="InvoiceID" type="xs:int" />
<xs:attribute name="Email" type="xs:string" />
<xs:attribute name="DomainID" type="xs:int" />
<xs:attribute name="SourceID" type="xs:int" />
<xs:attribute name="TypeEnum" type="xs:int" />
<xs:attribute name="ImportID" type="xs:int" />
<xs:attribute name="DateStamp" type="xs:dateTime" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 InvoiceID="55" Email="joeshmoe@joe.com" DomainID="2421"
SourceID="12" TypeEnum="2" ImportID="20"
DateStamp="2005-09-18T17:39:00-07:00" />
</NewDataSet>

This blows up in SQL when I call " sp_xml_preparedocume
nt".
It has trouble converting the DateStamp (because of the format)...
If I put it in Query Analyzer and remove "-07:00" from the DateStamp it
works fine....

Does anyone have any ideas?

ineedadip@gmail.com

2006-01-11, 11:24 am

nobody?

INeedADip

2006-01-11, 8:24 pm

Although I didn't "solve" the problem, I figured out how to work around it.
If you paste this in Query Analyzer it should work:
---------------------------------------
declare @x nvarchar(4000)
Select @x = N'<NewDataSet>
<xs:schema id="NewDataSet" xmlns=""
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true"
msdata:UseCurrentLoc
ale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="List_101">
<xs:complexType>
<xs:attribute name="InvoiceID" type="xs:int" />
<xs:attribute name="Email" type="xs:string" />
<xs:attribute name="DomainID" type="xs:int" />
<xs:attribute name="SourceID" type="xs:int" />
<xs:attribute name="TypeEnum" type="xs:int" />
<xs:attribute name="ImportID" type="xs:int" />
<xs:attribute name="DateStamp" type="xs:dateTime" />
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<List_101 InvoiceID="19066" Email="joe@joe.com" DomainID="2421"
SourceID="12" TypeEnum="2" ImportID="20"
DateStamp="2065-09-18T17:39:00-07:00" />
</NewDataSet>'

Declare @hDoc int
exec sp_xml_preparedocume
nt @hDoc OUTPUT, @x
-- Insert Into List_101
Select
InvoiceID, Email, DomainID, SourceID, TypeEnum, ImportID,
CAST(left(DateStamp,
10) AS datetime)
From
OPENXML(@hDoc, '/NewDataSet/List_101')
WITH (
InvoiceID Integer,Email varchar(50),DomainID
Integer,SourceID
Integer,TypeEnum Integer,ImportID Integer,DateStamp varchar(10))

Exec sp_xml_removedocumen
t @hDoc
---------------------------------------

I was trying to insert the XML data into a table called List_101, and that
table has a 'DateStamp' column that is a datetime type.
I kept getting the "Syntax error converting datetime from character string."
because of the DateTime format from DataSet.WriteXML();

So I ended up using the CAST(left(DateStamp,
10) AS datetime) so it was in
the correct format and changed the WITH to DateStamp varchar(10).
The sp_xml_preparedocume
nt doesn't blow up anymore and the data gets
successfuly inserted into List_101 with the proper DateStamp (which is a
datetime in the table) because it converts the varchar(10) on its own.

Hopefully this helps the next person.

Michael Rys [MSFT]

2006-01-12, 8:24 pm

You got the recommended workaround. SQL Server's datetime datatype does not
support timezone information.

Best regards
Michael

"INeedADip" <INeedADip@gmail.com> wrote in message
news:eRh4f5uFGHA.2300@TK2MSFTNGP15.phx.gbl...
> Although I didn't "solve" the problem, I figured out how to work around
> it.
> If you paste this in Query Analyzer it should work:
> ---------------------------------------
> declare @x nvarchar(4000)
> Select @x = N'<NewDataSet>
> <xs:schema id="NewDataSet" xmlns=""
> xmlns:xs="http://www.w3.org/2001/XMLSchema"
> xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
> <xs:element name="NewDataSet" msdata:IsDataSet="true"
> msdata:UseCurrentLoc
ale="true">
> <xs:complexType>
> <xs:choice minOccurs="0" maxOccurs="unbounded">
> <xs:element name="List_101">
> <xs:complexType>
> <xs:attribute name="InvoiceID" type="xs:int" />
> <xs:attribute name="Email" type="xs:string" />
> <xs:attribute name="DomainID" type="xs:int" />
> <xs:attribute name="SourceID" type="xs:int" />
> <xs:attribute name="TypeEnum" type="xs:int" />
> <xs:attribute name="ImportID" type="xs:int" />
> <xs:attribute name="DateStamp" type="xs:dateTime" />
> </xs:complexType>
> </xs:element>
> </xs:choice>
> </xs:complexType>
> </xs:element>
> </xs:schema>
> <List_101 InvoiceID="19066" Email="joe@joe.com" DomainID="2421"
> SourceID="12" TypeEnum="2" ImportID="20"
> DateStamp="2065-09-18T17:39:00-07:00" />
> </NewDataSet>'
>
> Declare @hDoc int
> exec sp_xml_preparedocume
nt @hDoc OUTPUT, @x
> -- Insert Into List_101
> Select
> InvoiceID, Email, DomainID, SourceID, TypeEnum, ImportID,
> CAST(left(DateStamp,
10) AS datetime)
> From
> OPENXML(@hDoc, '/NewDataSet/List_101')
> WITH (
> InvoiceID Integer,Email varchar(50),DomainID
Integer,SourceID
> Integer,TypeEnum Integer,ImportID Integer,DateStamp varchar(10))
>
> Exec sp_xml_removedocumen
t @hDoc
> ---------------------------------------
>
> I was trying to insert the XML data into a table called List_101, and that
> table has a 'DateStamp' column that is a datetime type.
> I kept getting the "Syntax error converting datetime from character
> string." because of the DateTime format from DataSet.WriteXML();
>
> So I ended up using the CAST(left(DateStamp,
10) AS datetime) so it was in
> the correct format and changed the WITH to DateStamp varchar(10).
> The sp_xml_preparedocume
nt doesn't blow up anymore and the data gets
> successfuly inserted into List_101 with the proper DateStamp (which is a
> datetime in the table) because it converts the varchar(10) on its own.
>
> Hopefully this helps the next person.
>

INeedADip

2006-01-28, 7:24 am

I also published the solution here:

http://ineedadip.blogspot.com/2006/...te-problem.html

Sponsored Links