XML Update Question

From: exBK (exBK_at_discussions.microsoft.com)
Date: 09/06/04 

Date: Mon, 6 Sep 2004 08:55:06 -0700

I am having trouble updating a row that has a string value for a bit column.
Below I have given my table def, stored proc and sample data. Can you please
tell me where I am going wrong ??

Table def:
CREATE TABLE [tblEmp] (
        [empID] [int] NOT NULL ,
        [fName] [varchar] (50) NULL ,
        [lName] [varchar] (50) NULL ,
        [isEmp] [bit] NULL ,
        CONSTRAINT [PK_tblEmp] PRIMARY KEY CLUSTERED (
                [empID]
        ) ON [PRIMARY]
) ON [PRIMARY]
GO

Stored Proc:
CREATE PROCEDURE addEmp
(@XMLEmp AS VARCHAR(8000))
AS
 DECLARE @hDoc int
 EXEC sp_xml_preparedocument @hDoc OUTPUT, @XMLEmp
 UPDATE tblEmp
 SET
   tblEmp.fName = XMLEmp.fName,
   tblEmp.lName = XMLEmp.lName,
   tblEmp.isEmp = CASE XMLEmp.isEmp WHEN UPPER('false') THEN 0 ELSE 1 END
 FROM OPENXML(@hDoc, '/reports/report',2)
       WITH tblEmp XMLEmp
 WHERE tblEmp.empID = XMLEmp.empID
 EXEC sp_xml_removedocument @hDoc
GO

How I call:
exec addEmp '<reports>
<report>
    <fName>Joe</fName>
    <lName>Smith</lName>
    <isEmp>False</isEmp>
    <empID>1</empID>
  </report>
</reports>'

when I run the above exec, I get "Syntax error converting the nvarchar value
'False' to a column of data type bit.". Not sure how to convert 'False' or
'True' to bit values when passing the XML as a string. TIA.

Relevant Pages

  • Re: func_code vs. string problem
    ... # we define 'print b' in three different ways: as a string, ... def execstring(): ... exec function.func_code ... strings and pass around as compiled objects, ...
    (comp.lang.python)
  • Re: how can I execute a function string
    ... > I have some code that generates a function on the fly in a string. ... exec generate_func ... A *slightly* better approach is to make a code object, using compile(). ... def gen_func: ...
    (comp.lang.python)
  • Re: how can I execute a function string
    ... > I have some code that generates a function on the fly in a string. ... s = """def f: ... For example, exec is picky ... SyntaxError: invalid syntax ...
    (comp.lang.python)
  • Re: Multi-line "Srchfor" Utility?
    ... specified on the command. ... for each EXEC CICS command, that literal is a bit string (arg 0 in the ... EXEC CICS GETMAIN, but beyond that I'd need to refer to the CICS Data ...
    (bit.listserv.ibm-main)
  • Re: full-text contains * issue
    ... You may also want to consider using a stored proc for this situation as it ... EXEC usp_FTSearchPubsInfo '' ... > no data but was concatenation of the fields I wanted to index. ... > This is the SQL ...
    (microsoft.public.sqlserver.fulltext)