Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    47

    Unanswered: can you explain me why my sp fails

    i have a stored procedure which is giving me a error because it has some errors
    the error is
    Code:
    The XML parse error 0xc00ce502 occurred on line number 1, near the XML text "<root><association module_Id=1 pageId=7 updatedBy=1 updateDateTime='(2012-05-20 14:56:02.897)'/></root>".
    Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
    The error description is 'A string literal was expected, but no opening quote character was found.'.
    Msg 8179, Level 16, State 5, Procedure usp_InsertUpdateModulePageAssociation, Line 12
    Could not find prepared statement with handle 0.
    The statement has been terminated.
    Code:
    my sp 
    create procedure usp_InsertUpdateModulePageAssociation
    @XMLDOC varchar(8000)
    AS
     
    declare @xml_hndl int
     
    --prepare the XML Document by executing a system stored procedure
    exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC
     
     
    --insert into table
    Insert Into Module_Page_Association
                (
                Module_ID, Page_ID, UpdatedBy,UpdatedDatetime
                )
    Select
                Module_ID, Page_ID, UpdatedBy, UpdatedDatetime
    From
                OPENXML(@xml_hndl, '/root/association', 1)
                With
                            (
                            Module_ID int '@module_Id',
                            Page_ID int '@pageId' ,
                            UpdatedBy int '@updatedBy',
                            UpdatedDatetime datetime '@updateDateTime'
                
                            )
    
                            exec usp_InsertUpdateModulePageAssociation '<root><association module_Id=1 pageId=7 updatedBy=1 updateDateTime=''(2012-05-20 14:56:02.897)''/></root>'

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That appears to be ill-formed XML. I think that the apostrophes around the time constant ought to be quotation marks.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2011
    Posts
    47
    Quote Originally Posted by Pat Phelan View Post
    That appears to be ill-formed XML. I think that the apostrophes around the time constant ought to be quotation marks.

    -PatP
    exec [usp_InsertUpdateModulePageAssociation]
    Code:
    '<root><association module_Id=1 pageId=7 updatedBy=1 updateDateTime="2012-05-20 14:56:02.897" /></root>'
    Tried this too, but this also gives the same error

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You don't post enough information for me to help fix your XML. Use the XML Validator to get the XML correct, then you ought to be able to run your procedure against the syntactically correct XML.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2011
    Posts
    47
    Quote Originally Posted by Pat Phelan View Post
    You don't post enough information for me to help fix your XML. Use the XML Validator to get the XML correct, then you ought to be able to run your procedure against the syntactically correct XML.

    -PatP
    Code:
    <?xml version="1.0" ?>  
    <root>
    <association module_Id=1 pageId=7 updatedBy=1 updateDateTime="2012-05-20 14:56:02.897" />
    </root>
    this is my complete xml, so now please help

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by navedjobs View Post
    Code:
    <?xml version="1.0" ?>  
    <root>
    <association module_Id=1 pageId=7 updatedBy=1 updateDateTime="2012-05-20 14:56:02.897" />
    </root>
    this is my complete xml, so now please help
    I have to say again:
    Quote Originally Posted by Pat Phelan View Post
    You don't post enough information for me to help fix your XML. Use the XML Validator to get the XML correct, then you ought to be able to run your procedure against the syntactically correct XML.

    -PatP
    Unless you can explain exactly what you expect your XML to represent (not just present the string, but explain what it means), I don't have the ability to help you to fix it... Use the XML Validator, it will show you what is syntactically incorrect, but you must decide what you want your syntax to describe and then fix the syntax so that it describes it.

    The XML you present is somewhat like the source code for a program. You're asking me to fix the program, but I don't know what the program is supposed to do... I can guess at what might be wrong but there is no way for me to be certain, only you can do that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2011
    Posts
    47

    Wink

    Quote Originally Posted by Pat Phelan View Post
    I have to say again:Unless you can explain exactly what you expect your XML to represent (not just present the string, but explain what it means), I don't have the ability to help you to fix it... Use the XML Validator, it will show you what is syntactically incorrect, but you must decide what you want your syntax to describe and then fix the syntax so that it describes it.

    The XML you present is somewhat like the source code for a program. You're asking me to fix the program, but I don't know what the program is supposed to do... I can guess at what might be wrong but there is no way for me to be certain, only you can do that.

    -PatP
    let me explain you what i am trying to do , the xml which I shown you contains values which i want to insert into my table , i m using this method because i have to perform bulk insertion at one go , i have written an sp which takes xml string as argument and does the insertion stuff for me , i have also shown you the sp in my first post of this topic.
    whatever i write aphostofi or quote to represent datetime in the xml , the xml validator gives me an error of some sort so i m getting pissed with this stuff and seeking your help , i guess the problem is with the datetime and not with the decimal numbers because in my table there are 3 columns which accepts an int, and one which accepts an datetime. so to cut it short how do i represent datetime in xml
    either this way "2012-05-20 14:56:02.897" or this way '2012-05-20 14:56:02.897 '

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have a look at this article. Especially the part about "XML Attribute Values Must be Quoted".

  9. #9
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    the xml validator gives me an error of some sort so i m getting pissed with this stuff and seeking your help
    Rather posting "it didn't work" (which is probably the biggest waste of time and forum space), you need to post what you tried and exactly which error(s) were raised by the xml validator.

    Consider that your validator issue(s) may be caused by the same problem/error that causes your SP to fail. . .

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Declare a table valued parameter, and pass a recordset to your procedure. Much simpler than monkeying with XML to accomplish something that SQL Server does well using standard tools.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •