Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GO
Change the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)
|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>
|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...[vbcol=seagreen]
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:

Bulk loading with OpenXML

I am trying to import some data into a SQLServer 2005 database using the
script below. However, I get the following error when I run my stored
procedure.
Cannot insert the value NULL into column 'ACCOUNT_ID', table
'PosDB.dbo.acctest'
; column does not allow nulls. INSERT fails.
The statement has been terminated.
I can't understand what this error really means. Why would account_id be
null? Can somebody enlighten me before I go totally insane ;-)
best regards
G?ran
-- SCRIPT BELOW --
CREATE TABLE acctest
(
ACCOUNT_ID int NOT NULL,
TEXT varchar (20) NULL ,
DEBIT tinyint NOT NULL ,
TALLYGROUP_ID smallint NOT NULL
)
GO
CREATE PROC MassLoadTest
AS
declare @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT,
'<DataSet>
<acctest account_id="1" text="VAT 25 %" debit="0" tallygroup_id="0" />
<acctest account_id="2" text="VAT 12 %" debit="0" tallygroup_id="0" />
<acctest account_id="3" text="VAT 6 %" debit="0" tallygroup_id="0" />
</DataSet>'
insert into acctest
select *
from openxml (@.hDoc, '/DataSet/acctest')
WITH acctest
exec sp_xml_removedocument @.hDoc
GO
MassLoadTest
GOChange the table creation script such that the case of the columns
matches the case in the xml
CREATE TABLE acctest
(
account_id int NOT NULL,
text varchar (20) NULL ,
debit tinyint NOT NULL ,
tallygroup_id smallint NOT NULL
)|||Thanks for the reply. That did indeed solve the problem... However, for
every answer there are usually two questions.. :-).
I guess that means that TransactSQL in some cases is case-sensitive? I
thought that sql was supposed to be case-insensitive? Is this a special
OpenXML behaviour?
G?ran
"markc600@.hotmail.com" wrote:

> Change the table creation script such that the case of the columns
> matches the case in the xml
> CREATE TABLE acctest
> (
> account_id int NOT NULL,
> text varchar (20) NULL ,
> debit tinyint NOT NULL ,
> tallygroup_id smallint NOT NULL
> )
>|||It is case sensitive since the column names are being translated into XPath
expressions that happen to be case sensitive. So TSQL is case insensitive
(unless you have set your collation to be case-sensitive), but XPath is
always case-sensitive.
Best regards
Michael
"Gran Kmpe" <GranKmpe@.discussions.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8-68FF47C69D99@.microsoft.com...
> Thanks for the reply. That did indeed solve the problem... However, for
> every answer there are usually two questions.. :-).
> I guess that means that TransactSQL in some cases is case-sensitive? I
> thought that sql was supposed to be case-insensitive? Is this a special
> OpenXML behaviour?
> Gran
> "markc600@.hotmail.com" wrote:
>

Bulk importing XML to SQL 2000

We are trying to do the Bulk importing XML to SQL 2000 and facing some
problem.
At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.
So, we try to follow the instruction in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
However, we found the XML format is different from the one in the web site.
It is similar as below:
<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E" Field6="F"
Field7="G" />
</TestingData>
This format can be used by OPENXML but cannot use the Bulk Importing.
I have set the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After running the VBScript, we found the number of records is correct but
all fields become null.
Is there other method we can do?
Ivan
I know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gb l...
> We are trying to do the Bulk importing XML to SQL 2000 and facing some
> problem.
> At first, we want to use OPENXML. We can use the bulk insert to import the
> XML file to the text field in a table but cannot pass the text data to the
> local variable as local variable does not support text and the XML is sure
> more than 8000 characters.
> So, we try to follow the instruction in
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
> However, we found the XML format is different from the one in the web
> site. It is similar as below:
> <TestingData>
> <Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
> Field6="F" Field7="G" />
> </TestingData>
> This format can be used by OPENXML but cannot use the Bulk Importing.
> I have set the schema as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Details" sql:relation="ImportTest" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Field1" type="xsd:string" />
> <xsd:element name="Field2" type="xsd:string" />
> <xsd:element name="Field3" type="xsd:string" />
> <xsd:element name="Field4" type="xsd:string" />
> <xsd:element name="Field5" type="xsd:string" />
> <xsd:element name="Field6" type="xsd:string" />
> <xsd:element name="Field7" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> After running the VBScript, we found the number of records is correct but
> all fields become null.
> Is there other method we can do?
> Ivan
>
|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:

> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MSFTNG P04.phx.gbl...
>
>

Bulk importing XML to SQL 2000

We are trying to do the Bulk importing XML to SQL 2000 and facing some
problem.
At first, we want to use OPENXML. We can use the bulk insert to import the
XML file to the text field in a table but cannot pass the text data to the
local variable as local variable does not support text and the XML is sure
more than 8000 characters.
So, we try to follow the instruction in
http://msdn.microsoft.com/library/d...>
oad_6bos.asp
However, we found the XML format is different from the one in the web site.
It is similar as below:
<TestingData>
<Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E" Field6="F"
Field7="G" />
</TestingData>
This format can be used by OPENXML but cannot use the Bulk Importing.
I have set the schema as follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Details" sql:relation="ImportTest" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="Field1" type="xsd:string" />
<xsd:element name="Field2" type="xsd:string" />
<xsd:element name="Field3" type="xsd:string" />
<xsd:element name="Field4" type="xsd:string" />
<xsd:element name="Field5" type="xsd:string" />
<xsd:element name="Field6" type="xsd:string" />
<xsd:element name="Field7" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
After running the VBScript, we found the number of records is correct but
all fields become null.
Is there other method we can do?
IvanI know the problem. The problem is in the schema.
Ivan
"Ivan" <ivan@.microsoft.com> glsD:ejty019LHHA.140@.TK2MSFTNGP04.phx.gbl...rkred">
> We are trying to do the Bulk importing XML to SQL 2000 and facing some
> problem.
> At first, we want to use OPENXML. We can use the bulk insert to import the
> XML file to the text field in a table but cannot pass the text data to the
> local variable as local variable does not support text and the XML is sure
> more than 8000 characters.
> So, we try to follow the instruction in
> http://msdn.microsoft.com/library/d...
kload_6bos.asp
> However, we found the XML format is different from the one in the web
> site. It is similar as below:
> <TestingData>
> <Details Field1="A" Field2="B" Field3="C" Field4="D" Field5="E"
> Field6="F" Field7="G" />
> </TestingData>
> This format can be used by OPENXML but cannot use the Bulk Importing.
> I have set the schema as follows:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="Details" sql:relation="ImportTest" >
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="Field1" type="xsd:string" />
> <xsd:element name="Field2" type="xsd:string" />
> <xsd:element name="Field3" type="xsd:string" />
> <xsd:element name="Field4" type="xsd:string" />
> <xsd:element name="Field5" type="xsd:string" />
> <xsd:element name="Field6" type="xsd:string" />
> <xsd:element name="Field7" type="xsd:string" />
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> After running the VBScript, we found the number of records is correct but
> all fields become null.
> Is there other method we can do?
> Ivan
>|||Hello,
The data you are trying to bulkload doesn't match the schema definition,
that's why you are getting NULLs.
The schema describes Field1, 2, 3... as elements but they are attributes in
the data file.So you have to change either the schema or the data to make it
work.
Hope this helps.
Regards,
Monica Frintu
"Ivan" wrote:

> I know the problem. The problem is in the schema.
> Ivan
> "Ivan" <ivan@.microsoft.com> ???g?ó?l￥ó·s?D:ejty019LHHA.140@.TK2MS
FTNGP04.phx.gbl...
>
>