Help on Insert XML

Question

Help on Insert XML

EIAnd

Right there with Babe

Points: 743
More actions
October 23, 2009 at 7:02 am

#156343

Hi,
Im trying to load an xls archive transforming into a XML and loading whit:
Declare @xdmInfo Int Execute sp_XML_PrepareDocument @xdmInfo Output, @Consumo
INSERT INTO CallList_Pruena_20091016 (I3_RowID, PHONENUMBER, Zone, Status, ICValue3, Attempts, Counter, I3_AttemptsAbandoned, I3_AttemptsMachine, I3_AttemptsBusy, I3_AttemptsFax, I3_AttemptsNoAnswer, MoneyCounter, Fecha, Tabla, Cliente, Sede, Nit, Telefono, Nombre, Dia) SELECT I3_RowID, PHONENUMBER, Zone, Status, ICValue3, Attempts, Counter, I3_AttemptsAbandoned, I3_AttemptsMachine, I3_AttemptsBusy, I3_AttemptsFax, I3_AttemptsNoAnswer, MoneyCounter, Fecha, Tabla, Cliente, Sede, Nit, Telefono, Nombre, Dia
from OpenXML( @xdmInfo, N'/Raiz/Info' )
With ( I3_RowID varchar(250), PHONENUMBER varchar(250), Zone varchar(250), Status varchar(250), ICValue3 varchar(250), Attempts varchar(250), Counter varchar(250), I3_AttemptsAbandoned varchar(250), I3_AttemptsMachine varchar(250), I3_AttemptsBusy varchar(250), I3_AttemptsFax varchar(250), I3_AttemptsNoAnswer varchar(250), MoneyCounter varchar(250), Fecha varchar(250), Tabla varchar(250), Cliente varchar(250), Sede varchar(250), Nit varchar(250), Telefono varchar(250), Nombre varchar(250), Dia varchar(250) ) Execute sp_XML_RemoveDocument @xdmInfo
but some columns have constraints like this:
ALTER TABLE [dbo].[CallList_Pruena_20091016 ] WITH NOCHECK ADD
CONSTRAINT [DF_CallList_Prueba_Zone] DEFAULT ('PCF') FOR [Zone],
CONSTRAINT [DF_CallList_Prueba_Status] DEFAULT ('') FOR [Status],
CONSTRAINT [DF_CallList_Prueba_ICValue3] DEFAULT ('CAMP_EPM_EMPRESARIAL_PORTAL_VIP') FOR [ICValue3],
CONSTRAINT [DF_CallList_Prueba_Fecha] DEFAULT (getdate()) FOR [Fecha],
CONSTRAINT [DF_CallList_Prueba_Tabla] DEFAULT ('CallList_Prueba') FOR [Tabla],
CONSTRAINT [DF_CallList_Prueba_Sede] DEFAULT ('Poblado') FOR [Sede],
CONSTRAINT [DF_CallList_Prueba_Cliente] DEFAULT ('Salida_EPM') FOR [Cliente]
GO
butt when im loading data whit the xml and the columns that by default when they are "" have to load the whit the default value of the constraint, it doesnt loading like that, it load a "".
why could that be?
could be when im constructing the xml of the data?
thank you any help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

This is because empty string is not a NULL, and your constraints accept empty strings. If you showed us your XML and also table schema, we might be able to come up with a fix for you.

Regards

Piotr

...and your only reply is slàinte mhath

EIAnd Right there with Babe Points: 743 More actions · Answer 2

Hi,

The XML Code is:

the table is:

CREATE TABLE [dbo].[CallList_EPM_Prueb3_Oct21](

[I3_RowID] [varchar](25) NOT NULL,

[PhoneNumber] [varchar](50) NOT NULL,

[Zone] [varchar](25) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Zone] DEFAULT ('PCF'),

[Status] [varchar](50) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Status] DEFAULT (''),

[ICValue3] [varchar](100) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_ICValue3] DEFAULT ('CAMP_EPM_PRUEB3'),

[Attempts] [int] NULL,

[Counter] [int] NULL,

[I3_AttemptsAbandoned] [int] NULL,

[I3_AttemptsMachine] [int] NULL,

[I3_AttemptsBusy] [int] NULL,

[I3_AttemptsFax] [int] NULL,

[I3_AttemptsNoAnswer] [int] NULL,

[UseDialPlan] [tinyint] NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_DialPlan] DEFAULT ('1'),

[MoneyCounter] [money] NULL,

[Fecha] [datetime] NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Fecha] DEFAULT (getdate()),

[Tabla] [varchar](100) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Tabla] DEFAULT ('CallList_EPM_Prueb3_Oct21'),

[Cliente] [varchar](100) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Cliente] DEFAULT ('Salida_EPM'),

[Sede] [varchar](100) NULL CONSTRAINT [DF_CallList_EPM_Prueb3_Oct21_Sede] DEFAULT ('Poblado'),

CONSTRAINT [PK_CallList_EPM_Prueb3_Oct21] PRIMARY KEY CLUSTERED

(

[I3_RowID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

Thank you very much any help, to solve these problem.