The XML parse error 0xc00ce556 occurred on line number 1

[vuyiswamb]

Good Day all

I have an SP that is defined like this

ALTER PROCEDURE [dbo].[sp_Constraints_Update] @xml ntext,
@TTBLType varchar(5)

AS

set nocount on

DECLARE @xmldoc int
DECLARE @sql varchar(8000)

-- In one long sql string do all of the following
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @xml



if exists (select * from [tempdb].[dbo].sysobjects where id = object_id(N'[tempdb].[dbo].[#Constraints]'))
  drop table [#Constraints]

-- turn the xml into a table with characteristics of the given table
SELECT *
INTO #Constraints
FROM OPENXML ( @xmldoc , '/Root/Row' , 2)
WITH     (
        [ID] int,        -- ID of the activitiy or contact (XM) to update
        DayOp char(7) 'Day_Action',
        [Day] int 'Day',
        SessOp char(7) 'Period_Action',
        Sess int 'Period'
       
    )

EXEC sp_xml_removedocument @xmldoc

--select * from #Constraints

update #Constraints  set [Day] = null where [Day] = 0    -- can't have [Day] <, = or > 0
update #Constraints  set [Sess] = null where [Sess] = 0    -- can't have [Day] <, = or > 0

-- todo make this generic for class or exam
update tbl_actv
    set [Day] = #Constraints.[Day],
        DayOp = case(#Constraints.[DayOp])
                    when 'on' then '='
                    when 'before' then '<'
                    when 'after' then '>'
                end,
        Session = #Constraints.Sess,
        SessOp = case(#Constraints.[SessOp])
                    when 'on' then '='
                    when 'before' then '<'
                    when 'after' then '>'
                end
    from #Constraints
    where #Constraints.ID = tbl_actv.ID

and i have have tried to execute it like this

exec sp_Constraints_Update 'EXAM','<Root><Row><ID>3</ID><Activity>ACC121 T1 [1]</Activity><Day_Action>on</Day_Action><Day>15</Day><Period_Action>on</Period_Action><Period>15</Period></Row><Row><ID>9</ID><Activity>ADM121 T1 [1]</Activity><Day_Action>on</Day_Action><Day>4</Day><Period_Action>on</Period_Action><Period>4</Period></Row><Row><ID>13</ID><Activity>ADM122 T1 [1]</Activity><Day_Action>on</Day_Action><Day>1</Day><Period_Action>on</Period_Action><Period>7</Period></Row><Row><ID>50</ID><Activity>BEC122 T1 [1]</Activity><Day_Action>on</Day_Action><Day>12</Day><Period_Action>on</Period_Action><Period>143</Period></Row><Row><ID>44</ID><Activity>BEC121 T1 [1]</Activity><Day_Action>on</Day_Action><Day>12</Day><Period_Action>on</Period_Action><Period>12</Period></Row><Row><ID>126</ID><Activity>MRK122 T1 [1]</Activity><Day_Action>on</Day_Action><Day>1</Day><Period_Action>on</Period_Action><Period>4</Period></Row><Row><ID>287</ID><Activity>ENGL321 T1 [1]</Activity><Day_Action>on</Day_Action><Day>1</Day><Period_Action>on</Period_Action><Period>21</Period></Row><Row><ID>288</ID><Activity>ENTR_E_221 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>289</ID><Activity>FRAN111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>290</ID><Activity>GRMN111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>291</ID><Activity>LATN111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>292</ID><Activity>LATN211 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>293</ID><Activity>LATN311 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>294</ID><Activity>LATN321 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>295</ID><Activity>LEER_E_111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>296</ID><Activity>LEER_R_111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>297</ID><Activity>LLBR111 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>298</ID><Activity>LLBR113 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>299</ID><Activity>LLBR114 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row><Row><ID>300</ID><Activity>LLBR211 T1 [1]</Activity><Day_Action>on</Day_Action><Day></Day><Period_Action>on</Period_Action><Period></Period></Row></Root>'

and i got the Following Error

The XML parse error 0xc00ce556 occurred on line number 1, near the XML text "EXAM".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Invalid at the top level of the document.'.
Msg 8179, Level 16, State 5, Procedure sp_Constraints_Update, Line 24
Could not find prepared statement with handle 0.
The statement has been terminated.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.
Msg 208, Level 16, State 0, Procedure sp_Constraints_Update, Line 40
Invalid object name '#Constraints'.

I thought the Problem was on C# side , while troubleshooting i came to the SP and found that the execution of the sp is a problem.

Thank you

[sknake]

ALTER PROCEDURE [dbo].[sp_Constraints_Update] @xml ntext,
@TTBLType varchar(5)

According to this the arguments are
1) XML
2) Table Type

exec sp_Constraints_Update 'EXAM','<XML DATA>'

I think you have the parameter order backwards

[vuyiswamb]

Today was a long day.

Thank you it worked.