Stored Procedure: datatype spørgsmål

Kan det der XML-værk ikke åbnes som tabel eller et eller andet (jeg har ikke meget erfaring med XML og SQL 2K).

Hvis ikke, så har du et problem :-/

Du skal kigge på WRITETEXT, som i stored procedures bruges til at putte ting ned i et text felt.

Hvad med bare at lave en temp. tabel som indeholder et sekvens nummer og er varchar(255). Du kan så ligge din XML ned i denne tabel med fortløbende sekvens numre. og hente dem ud en af gangen.

dbo.wf_xml_AddOrder_01 @P_XML ntext  AS

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @P_XML
SET @V_OrderID = (SELECT ORDER_ID FROM OPENXML (@idoc, \'/ROWSET/ROW\',2) WITH (ORDER_ID  int))


Dette udsnit af en procedure der anvender et XML dokument. Jeg lader dokumentet hænge i den parameter der leverer det til proceduren.

Denne procedure læser en ntext og gemmer denne lokalt i en række nvarchar for derefter at samle dem til en ntext. Dette har ikke noget med xml at gøre men løser et tilsvarende problem med eksekvering af lange SQL dokumenter der ligger i en tabel. Jeg håber at du kaa anvende dette.

CREATE PROCEDURE dbo.wf_version_control @p_newid integer output,@P_AppID integer = 0  AS
DECLARE @v_scid integer
DECLARE @v_sc_start integer
DECLARE @errcode int
DECLARE @SubScriptPos int
DECLARE @errStr nvarchar(255)
DECLARE @v_Test nvarchar(4000)
DECLARE @TestResult int
DECLARE @v_Short nvarchar(4000)
DECLARE @v_sql_a nvarchar(4000)
DECLARE @v_sql_b nvarchar(4000)
DECLARE @v_sql_c nvarchar(4000)
DECLARE @v_sql_d nvarchar(4000)
DECLARE @v_sql_e nvarchar(4000)
DECLARE @v_sql_f nvarchar(4000)
Create Table #ResultTbl (Result int)
Set @errcode = 0
Set @p_newid = 0
if (SELECT Count(*) FROM ac_setup WHERE setupid = \'script_no\') = 0
BEGIN
DELETE FROM ac_Countries
INSERT ac_countries (CountryID, Country, AdFormat) SELECT CountryID, Country, AdFormat FROM wfsys.dbo.ac_Countries
DELETE FROM ac_countries_dictionary
DELETE FROM ac_Countries_Index
SET IDENTITY_INSERT ac_Countries_index ON
INSERT ac_Countries_index (indexID,idx_Text) SELECT indexID,idx_Text From wfsys.dbo.ac_Countries_index
INSERT ac_Countries_dictionary (countryID,indexID,dic_text) SELECT countryID,indexID,dic_text FROM wfsys.dbo.ac_Countries_dictionary
DELETE FROM ac_postalCodes
INSERT ac_postalCodes (CountryID, PostalCode, City, State) SELECT CountryID, PostalCode, City, State FROM wfsys.dbo.ac_postalCodes
DELETE FROM wf_reports
DELETE FROM wf_reports_dictionary
INSERT wf_reports  (RepType,Rid,Description,ReportName,Parameter) SELECT RepType,Rid,Description,ReportName,Parameter FROM wfsys.dbo.wf_reports
INSERT wf_reports_dictionary  (RepType, Rid, CountryID, Description) SELECT RepType, Rid, CountryID, Description FROM wfsys.dbo.wf_reports_dictionary
SELECT @v_sc_start = max(scID) FROM wfsys.dbo.wf_scripts
  INSERT ac_setup (setupid,setupno) VALUES (\'script_no\',@v_sc_start)
END ELSE BEGIN
  set @v_sc_start = (SELECT setupno FROM ac_setup WHERE setupid = \'script_no\')
END
If @P_AppID = 0 BEGIN Set @P_AppID = (SELECT max(scID) FROM  Wfsys.dbo.wf_scripts)  END
if @P_AppID >= @v_sc_start BEGIN
Set @v_scid = @v_sc_start
DECLARE sc_cursor CURSOR FOR SELECT scid,
WF_Test,
wf_ShortScript,
SUBSTRING(wf_script,1,4000),
SUBSTRING(wf_script,4001,4000),
SUBSTRING(wf_script,8001,4000),
SUBSTRING(wf_script,12001,4000),
SUBSTRING(wf_script,16001,4000),
SUBSTRING(wf_script,20001,4000)
FROM wfsys.dbo.wf_scripts WHERE scID > @v_sc_start  AND scID <= @P_AppID  ORDER BY scID
OPEN sc_cursor
FETCH NEXT FROM sc_cursor INTO @v_scid, @v_Test, @v_Short, @v_sql_a,@v_sql_b,@v_sql_c,@v_sql_d,@v_sql_e,@v_sql_f
WHILE @@FETCH_STATUS = 0
BEGIN
    set @SubScriptPos = 0
    Insert into #ResultTbl Exec (@v_test)
    set @errcode =  @@error
    if @errcode  <> 0  goto errormessage
    set @TestResult=(Select Result from #ResultTbl)
    delete from #ResultTbl
    if ((@TestResult=1) or (@TestResult=3))
    Begin
        set @SubScriptPos = 1
        Exec (@v_Short)
    end
    set @errcode =  @@error
    if @errcode  <> 0  goto errormessage
    if ((@TestResult=2) or (@TestResult=3))
    Begin
        set @SubScriptPos = 2
        EXEC  (@V_Sql_a + @V_Sql_b + @V_Sql_c + @V_Sql_d + @V_Sql_e + @V_Sql_f)
    End
    set @errcode =  @@error
    if @errcode  <> 0  goto errormessage
    FETCH NEXT FROM sc_cursor INTO @v_scid, @v_Test, @v_Short, @v_sql_a,@v_sql_b,@v_sql_c,@v_sql_d,@v_sql_e,@v_sql_f
END
errormessage:
if @errcode  <> 0 BEGIN
set @errStr = cast(@v_scid  as nvarchar) + \' - \' + cast(@SubScriptPos  as nvarchar) + \' - wf_version_control. Testresult:\' + cast(@TestResult  as nvarchar)
exec wf_ac_AddLog @P_CompID = -1, @P_Message =  @errStr,  @P_errcode =  @errcode
Set @v_scid = @v_scid - 1
END
close sc_cursor
Drop Table #ResultTbl
DEALLOCATE sc_cursor
if @v_scid > @v_sc_start BEGIN
UPDATE ac_setup SET setupno = @v_scid WHERE setupid = \'script_no\'
END
Set @p_newid = @v_scid
END ELSE BEGIN
Set @p_newid = @P_AppID
END