NULL values in XML string

Can someone tell me how to format my XML string so when I load it into my
table the field actually has a NULL value and not an empty space?
My XML string is formated like this.
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type='INSTIT' Provider_SubType='11' ReimDesc=""
Status='PMA' CreateUsr='Test' LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement><
The problem is that I want the ones with nothing between the doublw quotes
to get entered into the table as NULL values not empty strings. I am having
difficulty doing this. Can someone help me? Thanks in advance.>> The problem is that I want the ones with nothing between the doublw
What is the datatype of the columns in the table? Do you have a default set
for these columns, if the values are missing?Why not post the DDLs here? Are
you using OPENXML to load the data or using some other 3rd party tools?
Anith|||CREATE TABLE [tbl_Reimbursement] (
[ReimID] [int] IDENTITY (1, 1) NOT NULL ,
[RecordType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_Reimbursement_RecordType] DEFAULT (10),
[ActionType] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_ActionType] DEFAULT ('A'),
[Provider_Type] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Provider_SubType] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NetworkCode] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF_tbl_Reimbursement_NetworkCode] DEFAULT (' '),
[EffectiveDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_EffectiveDate] DEFAULT ('6/1/2004'),
[ExpirationDate] [datetime] NOT NULL CONSTRAINT
[DF_tbl_Reimbursement_ExpirationDate] DEFAULT ('12/31/2099'),
[ReimDesc] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SubmitDate] [datetime] NULL ,
[SubmitUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportID] [int] NULL ,
[ExportDate] [datetime] NULL ,
[ApproveDate] [datetime] NULL ,
[ApproveUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NULL CONSTRAINT [DF_tbl_Reimbursement_CreateDate]
DEFAULT (getdate()),
[CreateUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExportSeq] [int] NULL CONSTRAINT [DF_tbl_Reimbursement_ExportSeq] DEFAULT
(1),
[LastModDate] [datetime] NULL ,
[LastModUsr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReimLocked] [bit] NULL ,
[ReimLockedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Reimbursement] PRIMARY KEY CLUSTERED
(
[ReimID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
) ON [PRIMARY]
GO
insert into tbl_Reimbursement (Provider_Type, Provider_SubType, ReimDesc,
Status, CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr)
select * from OPENXML (@.idoc,'/reimbursement/tbl_Reimbursement/Type10Rec',1)
with (Provider_Type varchar(6),
Provider_SubType varchar(2),
ReimDesc varchar(70),
Status varchar(10),
CreateUsr varchar(50),
LastModDate datetime,
LastModUsr varchar(50),
ApproveDate datetime,
ApproveUsr varchar(50))
"Anith Sen" wrote:

> What is the datatype of the columns in the table? Do you have a default se
t
> for these columns, if the values are missing?Why not post the DDLs here? A
re
> you using OPENXML to load the data or using some other 3rd party tools?
> --
> Anith
>
>|||Your XML, as you posted in your initial post, is not well formed. It has no
closing tags for reimbursement node. The XML string has only eight values,
so it will insert values for only eight corresponding columns mentioned in
the INSERT statement. If the other columns are nullable, NULLs will enter
those columns. Here is the repro:
DECLARE @.x VARCHAR(500), @.idoc INT
SET @.x = '
<reimbursement>
<tbl_Reimbursement>
<Type10Rec Provider_Type="INSTIT" Provider_SubType="11" ReimDesc=""
Status="PMA" CreateUsr="Test" LastModDate="" LastModUsr="" ApproveDate=""/>
</tbl_Reimbursement>
</reimbursement>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.x
INSERT INTO tbl
( Provider_Type, Provider_SubType, ReimDesc, Status,
CreateUsr, LastModDate, LastModUsr, ApproveDate, ApproveUsr )
SELECT * -- use column names
FROM OPENXML ( @.idoc, '/reimbursement/tbl_Reimbursement/Type10Rec', 1 )
WITH ( Provider_Type VARCHAR( 6 ),
Provider_SubType VARCHAR( 2 ),
ReimDesc VARCHAR( 70 ),
Status VARCHAR( 10 ),
CreateUsr VARCHAR( 50 ),
LastModDate DATETIME,
LastModUsr VARCHAR( 50 ),
ApproveDate DATETIME,
ApproveUsr VARCHAR( 50 ) )
EXEC sp_xml_removedocument @.idoc
Anith