November 4, 2010 at 1:29 pm
Hi All,
I am using SQLXMLBulkLoad to insert records into a existing db (SQL Server 2005) but I am getting the following error. I have no idea why I am getting this error.
</SQLState><NativeError></NativeError><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Invalid character value for cast specification.]]></Description></Record></Error>
The XML I am sending is (I have reduced it one record to make it easy)<ROOT>
<LNReportResultItems ReportItemIndex="0" ReportGUID="c991f7f9-b883-4dd2-8cde-13c50104cce0" ItemType="5" ItemID="3" SubItemID="10" Location1="0" Location2="2" Location3="0" Location4="14" Page="1" ConflateFlag="1" ConflateNdx="0"/>
</ROOT>
xdr schema is <?xml version="1.0" encoding="windows-1252" ?>
<!-- Generated by XMLMapper.exe XDR Publisher -->
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<ElementType name="LNReportResultItems"
content="mixed"
order="many" >
<AttributeType name="ReportItemIndex"
dt:type="int" >
</AttributeType>
<AttributeType name="ReportGUID"
dt:type="string" >
</AttributeType>
<AttributeType name="ItemType"
dt:type="int" >
</AttributeType>
<AttributeType name="ItemID"
dt:type="int" >
</AttributeType>
<AttributeType name="SubItemID"
dt:type="int" >
</AttributeType>
<AttributeType name="Location1"
dt:type="int" >
</AttributeType>
<AttributeType name="Location2"
dt:type="int" >
</AttributeType>
<AttributeType name="Location3"
dt:type="int" >
</AttributeType>
<AttributeType name="Location4"
dt:type="int" >
</AttributeType>
<AttributeType name="Page"
dt:type="int" >
</AttributeType>
<AttributeType name="ConflateFlag"
dt:type="int" >
</AttributeType>
<AttributeType name="ConflateNdx"
dt:type="int" >
</AttributeType>
<AttributeType name="GroupIndex"
dt:type="int" >
</AttributeType>
<attribute type="ReportItemIndex"
required="no" >
</attribute>
<attribute type="ReportGUID"
required="no" >
</attribute>
<attribute type="ItemType"
required="no" >
</attribute>
<attribute type="ItemID"
required="no" >
</attribute>
<attribute type="SubItemID"
required="no" >
</attribute>
<attribute type="Location1"
required="no" >
</attribute>
<attribute type="Location2"
required="no" >
</attribute>
<attribute type="Location3"
required="no" >
</attribute>
<attribute type="Location4"
required="no" >
</attribute>
<attribute type="Page"
required="no" >
</attribute>
<attribute type="ConflateFlag"
required="no" >
</attribute>
<attribute type="ConflateNdx"
required="no" >
</attribute>
<attribute type="GroupIndex"
required="no" >
</attribute>
</ElementType>
</Schema>
Just to help I am also posting output of
SELECT *
FROM LNReportResultItems
FOR XML RAW('LNReportResultItems'), XMLSCHEMA('urn:example.com')
<xsd:schema targetNamespace="urn:example.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="LNReportResultItems">
<xsd:complexType>
<xsd:attribute name="ReportItemIndex" type="sqltypes:int" />
<xsd:attribute name="ReportGUID" type="sqltypes:uniqueidentifier" />
<xsd:attribute name="ItemType" type="sqltypes:int" />
<xsd:attribute name="ItemID" type="sqltypes:int" />
<xsd:attribute name="SubItemID" type="sqltypes:int" />
<xsd:attribute name="Location1" type="sqltypes:int" />
<xsd:attribute name="Location2" type="sqltypes:int" />
<xsd:attribute name="Location3" type="sqltypes:int" />
<xsd:attribute name="Location4" type="sqltypes:int" />
<xsd:attribute name="Page" type="sqltypes:int" />
<xsd:attribute name="ConflateFlag" type="sqltypes:int" />
<xsd:attribute name="ConflateNdx" type="sqltypes:int" />
<xsd:attribute name="GroupIndex" type="sqltypes:int" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
Thanks
Brijesh Sharma
November 4, 2010 at 1:39 pm
Since you're using SQL 2005 (per the forum you posted in), not SQL 2000, is there any reason to not use OpenRowset Bulk, and then parse the XML using XQuery? That's usually a LOT easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 1:42 pm
I isolated the problem is with the guid field. So any idea how I should be sending a uniqueidentifier field through xml.
I am currently using BULK INSERT but my client's sql server cannot access the bulk insert file
So Once I get SQLXMLBulkLoad working using a file, I want to move it to use a stream. Hope that explains
November 4, 2010 at 1:43 pm
Try sending it as a string, then converting it once shredded.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 4, 2010 at 1:46 pm
That is what I am doing I guess
In the schema its is defined as
<AttributeType name="ReportGUID"
dt:type="string" >
The xml contains it as
ReportGUID="c991f7f9-b883-4dd2-8cde-13c50104cce0"
November 4, 2010 at 3:07 pm
Posted a short and more precise question as a new topic
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply