OpenXML feature of SQL Server2000

OpenXML feature of SQL Server2000

The SQL Server2000 provides OpenXML feature, which we can easily use the OpenXML feature instead of the traditional row assembly, and we can also use the defined XML document as the input data, which is also huge for our data transplant. convenient.

At the same time, the SQL Server 2000 also provides a stored procedure. In Microsoft, only the design is designed to store procedures, it is "Clearator Separation of code from the middle-tier", which is good. The data of the application accesses the database is not directly dealing with the database table, but the required data is obtained by the operation of the stored procedure. Such a design has a benefit to avoid frequent table operations, and the storage procedure running on the server can greatly improve the operation efficiency and improve the speed of access data, and also shield the logic of the database table, making Database access becomes a service access available to the database.

Since OpenXML and storage procedures can improve the performance of SQL Server2000, can you combine this technique? The answer is yes, let's take a look at how to use OpenXML during the stored procedure.

The following is a stored procedure that uses OpenXML features:

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

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

- UparchiveMsginsertmsg

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

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

This is an example of a document message XML document

Declare @xml varchar (8000)

Set @xml = '

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

Sendtime = "

1/1/2001

"Department =" mse "

Attachfile = "hust"

ReceiveList = "YOUNTHER" body = "hustwelcome">

'

Exec uparchivemsginsertmsg @XML

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

// This run stored procedure first inserts a document information in the ArchiveMsg table, and in accordance with the MSGID and recipient list of the document information, the MSGLIST table is inserted into the UserID and MSGID of each recipient, which is obtained from XML.

Alter Procedure UparchiveMsgInsertmsg

(

@xml varchar (8000)

)

AS

Declare @idoc int - XML ​​DOC

Declare @msgid int - new Order

Declare @sendtime datetime

- Analysis XML document

EXEC SP_XML_PREPAREDocument @idoc output, @XML

Set nocount on

Declare @currentError Int

Begin Transaction

- Start transaction for updating data

SELECT @ sendtime = sendtime

From OpenXML (@IDOC, '/ ArchiveMsg')

With archivemsg

INSERT INTO Archivemsg (Title, Userid, Author, Sendtime, Department, ReceiveList, body, attachfile)

Select Title, Userid, Author, Sendtime, Department, ReceiveList, 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

- End of the transaction

Commit transaction

Set nocount off

- Remove the XML document from the 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

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

Ok, the stored procedure is like this, we have to test this stored procedure, the test function code is as follows:

First we have to generate this XML document. For the sake of simplicity, we use a String variable to play XML documents:

Public string getXmlstring (ArchiveDetail mymsg, string "useridlist)

{

String XML = "

XML = "

XML = "UserID = /" " mymsg.userid " / ""

XML = "Author = /" " mymsg.author " / ""

XML = "sendtime = /" " mymsg.sendtime.toString () " / "";

XML = "Department = /" " mymsg.Department " / ""

XML = "attachfile = /" " mymsg.attachfilepath " / ""

XML = "ReceiveList = /" " mymsg.receivelist " / ""

XML = "Body = /" " mymsg.body " / ">";

FOREACH (String I IN UseridList)

{

XML = "";

}

XML = "";

Return XML;

}

Then call this stored process:

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");

C.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 ();

Conit (); 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 more, but the actual processing process is actually relatively simple, which is approximately as follows:

Define XML text

In the stored procedure, XML is introduced, using SQL Server's Open XML pair (specifically several functions related to XML)

Call the stored procedure in the program

end.

In this way we can easily combine XML and SQL Server to apply.

Of course, SQL Server's support is not only here. , Please also ask your netizens to criticize.