MS SQL XML - Bulk loading with OpenXML

Author

Bulk loading with OpenXML

GÃ¶ran KÃ¤mpe

2005-09-29, 3:24 am

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_preparedocume
nt @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_removedocumen
t @hDoc
GO

MassLoadTest
GO

markc600@hotmail.com

2005-09-29, 7:24 am

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
)

GÃ¶ran KÃ¤mpe

2005-09-29, 7:24 am

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
> )
>
>

Michael Rys [MSFT]

2005-10-01, 8:23 pm

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

"Göran Kämpe" < GranKmpe@discussions
.microsoft.com> wrote in message
news:6E6B939C-8B98-4185-A2F8- 68FF47C69D99@microso
ft.com...[color=darkred]
> 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:
>

Sponsored Links