Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-ak
Using SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/
|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capturing Invalid Records for XML Inserts

Greetings. I'm new to Xml inserts into sql server. I've used "OpenXML"
to conduct an insert for single rows. But, I have a case where I'd like
to use the bulk insert method and am wondering if there's a way to
capture invalid records from the bulk insert, while still inserting the
valid ones.
Thanks.
-akUsing SqlXmlBulkload, your only option is to preprocess the Xml source with
XSLT to enforce your business rules or to load the data into temp tables
and post process.
Andrew Conrad
Microsoft Corp
http://blogs.msdn.com/aconrad/|||Thanks Andrew.
"Andrew Conrad" wrote:
> Using SqlXmlBulkload, your only option is to preprocess the Xml
source with
> XSLT to enforce your business rules or to load the data into temp
tables
> and post process.
> Andrew Conrad
> Microsoft Corp
> http://blogs.msdn.com/aconrad/

Capitalizing First Letter, and Lower the rest

I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion

I am getting this error:

Invalid length parameter passed to the RIGHT function.

This error occurs because you have a record that does not have religion specified (it's either NULL or an empty string). You can test for this using the case statement. When the length of the religion field is 0, then an empty string will be returned. If it's not zero, then your code kicks in and the first letter is capatalized.

Case When Len(religion) = 0 then '' else (left(religion,1))+ lower(right(religion,len(religion)-1)) end As Religion

|||

Ray,

Please try this function or set of functions. The first one is ProperWord and the other is ProperString. One calls the other, but if you are only doing one word, then you can just use ProperWord. Feel free to email me for the formatted version of the code.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[ProperWord](@.str VARCHAR(100))

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @.v_Len INT,

@.v_Value VARCHAR(100)

SET @.v_Len = LEN(@.str)

IF @.v_Len > 1

SET @.v_Value = UPPER(LEFT(@.str,1)) + LOWER(RIGHT(@.str,@.v_len - 1))

ELSE

SET @.v_Value = UPPER(@.str)

RETURN @.v_Value

END

GO

ALTER FUNCTION [dbo].[ProperString](@.str VARCHAR(MAX))

RETURNS VARCHAR(MAX)

AS

BEGIN

DECLARE @.v_Len INT,

@.v_Value VARCHAR(MAX),

@.v_Word VARCHAR(100),

@.v_Pos INT

SET @.v_Pos = CHARINDEX(' ', @.str)

WHILE @.v_Pos > 0

BEGIN

SELECT @.v_Len = LEN(@.str),

@.v_Word = LTRIM(RTRIM(LEFT(@.str,@.v_Pos))),

@.str = LTRIM(RTRIM(RIGHT(@.str, @.v_Len - @.v_Pos))),

@.v_Word = dbo.ProperWord(@.v_Word),

@.v_Value = COALESCE(@.v_Value + ' ' + @.v_Word, @.v_Word),

@.v_Pos = CHARINDEX(' ', @.str)

END

SELECT @.v_Word = dbo.ProperWord(LTRIM(RTRIM(@.str))),

@.v_Value = COALESCE(@.v_Value + ' ' + @.v_Word, @.v_Word)

RETURN @.v_Value

END

|||

Forgot the UPPER :)

declare @.religion varchar(100)
set @.religion = 'a'
select Case When Len(@.religion) = 0 then '' else (upper(left(@.religion,1)))+ lower(right(@.religion,len(@.religion)-1)) end As Religion

|||Mastros this worked great, everyone else thanks too.