Openxml features of SQL Server2000

SQL Server2000 provides the openxml feature. We can easily use the openxml feature to replace the traditional row set results. Similarly, we can also use the defined XML document as the input data, this also brings great convenience to our data migration.

At the same time, SQL Server2000 also provides stored procedures. In Microsoft's words, only stored procedures can be regarded as "Cleaner separation of code from the middle-tier ", this is a good practice. ApplicationProgramAccessing database data does not directly deal with database tables, but obtains the required data through running the stored procedure. One benefit of this design is that frequent table operations are avoided, while the storage process running on the server can greatly improve the operational efficiency and speed up data access, at the same time, the database table logic is well shielded, making database access a service access provided by the database.

Since both openxml and stored procedures can improve the performance of SQL Server2000, can this technology be combined? The answer is yes. Let's take a look at how to use openxml In the stored procedure.

The following is a stored procedure that uses the openxml feature:

**************************************** ***************************************

 

-------------------------------------------------------------------------------

-- Uparchivemsginsertmsg

-------------------------------------------------------------------------------

/*************************************** ***************************************

This is an example of a document Message XML document.

Declare @ XML varchar (8000)

Set @ xml = '<? XML version = "1.0" encoding = "gb2312"?>

<Archivemsg

Title = "jiangsuer" userid = "admin" author = "Hubei"

Sendtime = "1/1/2001" department = "MSE"

Attachfile = "HUST"

Cancelist = "younther" Body = "hustwelcome">

<Msglist userid = "admin"/>

<Msglist userid = "Anime"/>

<Msglist userid = "tiger"/>

</Archivemsg>'

 

Exec uparchivemsginsertmsg @ XML

**************************************** ***************************************/

// This stored procedure inserts a document in the archivemsg table, and inserts the userid and msgid of each recipient in the msglist table based on the msgid and recipient list of the document information, this information is obtained from XML.

 

Alter procedure uparchivemsginsertmsg

(

@ XML varchar (8000)

)

As

Declare @ IDOC int -- XML Doc

Declare @ msgid int -- New Order

Declare @ sendtime datetime

-- Parse XML documents

Exec sp_xml_preparedocument @ IDOC output, @ XML

Set nocount on

Declare @ currenterror int

Begin transaction

-- Start the data update transaction

Select @ sendtime = sendtime

From openxml (@ IDOC, '/archivemsg ')

With archivemsg

Insert into archivemsg (title, userid, author, sendtime, department, cancelist, body, attachfile)

Select title, userid, author, sendtime, department, cancelist, body, attachfile

From openxml (@ IDOC, '/archivemsg ')

With archivemsg

-- Error check

Select @ currenterror = @ Error

If @ currenterror! = 0

Begin

Goto error_handler

End

Select @ msgid = @ identity

Insert into msglist (msgid, userid, sendtime)

Select @ msgid, userid, @ sendtime

From openxml (@ IDOC, '/archivemsg/msglist ')

With (

Msgid int,

Userid varchar (80 ),

Sendtime datetime

)

-- Error check

Select @ currenterror = @ Error

 

If @ currenterror! = 0

Begin

Goto error_handler

End

-- Transaction ends

Commit transaction

Set nocount off

-- Remove the defined XML document from memory

Exec sp_xml_removedocument @ IDOC

 

Return 0

Error_handler:

Rollback transaction

Set nocount off

-- Remove the defined XML document from memory

Exec sp_xml_removedocument @ IDOC

Return @ currenterror

**************************************** ***************************************

Okay, that's the stored procedure. We have to test the stored procedure and test the function.CodeAs follows:

First, we need to generate this XML document. For convenience, we will use a string variable to simulate the XML document:

Public String getxmlstring (archivedetail mymsg, string [] useridlist)

{

String xml = "<? XML version = \ "1.0 \" encoding = \ "gb2312 \"?> ";

XML + = "<archivemsg ";

XML + = "Title = \" "+ mymsg. Title + "\"";

XML + = "userid = \" "+ mymsg. userid + "\"";

XML + = "author = \" "+ mymsg. Author + "\"";

XML + = "sendtime = \" "+ mymsg. sendtime. tostring () + "\"";

XML + = "department = \" "+ mymsg. Department + "\"";

XML + = "attachfile = \" "+ mymsg. attachfilepath + "\"";

XML + = "cancelist = \" "+ mymsg. cancelist + "\"";

XML + = "Body = \" "+ mymsg. Body +" \ "> ";

Foreach (string I in useridlist)

{

XML + = "<msglist userid = \" "+ I +" \ "/> ";

}

XML + = "</archivemsg> ";

Return XML;

}

Then the stored procedure function is called:

Public bool sendarchivemsg (archivedetail mymsg, string [] useridlist)

{

String xml = This. getxmlstring (mymsg, useridlist );

Try

{

Sqlparameter [] prams = {data. makeinparam ("@ XML", sqldbtype. varchar, 8000, XML)

};

Sqlconnection con = new

Sqlconnection ("Server = 127.0.0.1; uid = sa; Pwd = sa; database = mydatabase ");

Con. open ();

Sqlcommand cmd = new sqlcommand ("uparchivemsginsertmsg", con );

Cmd. commandtype = commandtype. storedprocedure;

Foreach (sqlparameter parameter in prams)

Cmd. Parameters. Add (parameter );

 

Cmd. Parameters. Add (

New sqlparameter ("returnvalue", sqldbtype. Int, 4,

Parameterdirection. returnvalue, false, 0, 0,

String. Empty, datarowversion. Default, null ));

 

Cmd. executenonquery ();

Con. Close ();

Int retval = (INT) cmd. Parameters ["returnvalue"]. value;

If (retval = 0)

Return true;

Else

Return false;

}

Catch (exception ex)

{

Error. Log (ex. tostring ());

Return false;

}

}

 

 

In the above Code, the amount of code is relatively large, but the actual process is actually relatively simple, roughly as follows:

Define XML

Introduce XML into the stored procedure and use SQL Server's open XML to process it (specifically, several XML-related functions)

Call a stored procedure in a program

End.

In this way, we can easily combine XML and SQL Server for application.

Of course, SQL Server's support for XML is not only here. If you have any concerns, you can see that SQLXML can also be configured in IIS. The relevant content is not detailed or inappropriate in the text, I also ask all netizens to criticize and correct them.