Post reply

passing xml to database

  • September 26, 2002 at 2:58 am

    #79714

    Hi,

    I have a table that one of its fields type is text.

    i want to insert a new record to this table so, I'm passing an xml string to the stored

    procedure which is...

    AddNewJob_SP

    @XMLStringTEXT,

    @GlobalIdINT OUTPUT

    AS

    DECLARE @hDocINT

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XMLString

    INSERT INTO Jobs

    SELECT *

    FROM OPENXML (@hDoc, '/Field',2) WITH

    (JobNumberVARCHAR(50),

    JobTitleVARCHAR(100),

    JobDescriptionTEXT)

    now, for the problem, as you can see the JobDescription is text, and i get an error for that, if i change the type to varchar(8000) the error is gone.

    What do i do? (and i need more then 8000 characters.

  • October 12, 2002 at 6:49 am

    #438408

    What error do you get?

    I have created a jobs table and tried your sp and don't get a problem. Is the column nullable?

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 13, 2002 at 9:38 am

    #438446

    Thank you for your answer.

    There is no problem creating the stored procedure, the problem is calling it from asp page like this:

    ************Start Code*******************

    Function ExecuteSP(sXmlString)

    Dim ObjCmdExecuteSP

    Set ObjCmdExecuteSP = Server.CreateObject("ADODB.Command")

    With ObjCmdExecuteSP

    .ActiveConnection = ObjGlobalConnection

    .CommandType = adCmdStoredProc

    .CommandText = "AddNewJob_SP"

    .Parameters.append ObjCmdExecuteSP.CreateParameter("@XmlString",adLongVarChar,adParamInput,,sXmlString)

    .Parameters.append ObjCmdExecuteSP.CreateParameter("@GlobalId",adInteger,adParamInputOutput,4,0)

    .Execute

    ExecuteSP = .Parameters("@GlobalId")

    End With

    Set ObjCmdExecuteSP = Nothing

    End Function

    **************End Code************

    it seems i can't call this stored procedure with text length.

    Thanks in advance.

    Yossi

    Edited by - yossi on 10/13/2002 09:38:58 AM

  • October 13, 2002 at 3:37 pm

    #438449

    When you create a parameter for any string variable, char, nchar, varchar, nvarchar, text of ntext you have to specify a length. The simplest solution in your case would be to use len(sXMLString). I can't remember what the largest size you can specify is at the moment. If you call the .refresh method on the parameters collection of the command object all these properties will be filled in for you. You should however never use .refresh in production code due to sql calls made to obtain the parameter info.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

  • October 14, 2002 at 2:19 am

    #438468

    When i try to specify the length of charcter that is more then 8000 characters (only then) i get an error message that says.

    "Microsoft OLE DB Provider for SQL Server

    Code pagetranslations are not supported for the text data page. From: 1252 to: 1255".

    If the length is less then 8000 i don't get any error.

  • October 14, 2002 at 1:39 pm

    #438534

    Ok the problem I think is now that the codepage of the database does not match that of the client. I believe you will have to change either one. One is setup with hebrew and the other latin.

    You can change the client collation under regional settings.

    Simon Sabin

    Co-author of SQL Server 2000 XML Distilled

    http://www.amazon.co.uk/exec/obidos/ASIN/1904347088


    Simon Sabin
    SQL Server MVP

    http://sqlblogcasts.com/blogs/simons

Viewing 6 posts - 1 through 5 (of 5 total)

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