Go4Expert - Change method to send sql server sproc XML

Go4Expert (http://www.go4expert.com/)

- SQL Server (http://www.go4expert.com/forumdisplay.php?f=40)

- - Change method to send sql server sproc XML (http://www.go4expert.com/showthread.php?t=5310)

Change method to send sql server sproc XML

In sql server 2000, to send an xml document to a stored procedure, I needed to use the following method:

Here is the C# .NET code to prepare the xml to be sent to sproc.

Code:

con.Open();

SqlCommand cmd = new SqlCommand("ScanResults", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@doc", SqlDbType.NText).Value = XMLReturn;

SqlDataAdapter adp = new SqlDataAdapter(cmd);

adp.Fill(dataset, "ID");

con.Close();

Here is the how the sproc handles this:

Code:

CREATE PROCEDURE [csTest].[ScanResults] (@doc ntext)



AS

DECLARE @iTree int

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc



~buncha crap~



EXEC sp_xml_removedocument @iTree

        

RETURN

Now I am working with sql server 2005, and from what I understand it has the ability to send actualy xml document types to sprocs... I would like to know how to change the sproc and code snippet to use this new method. Thanks.

Re: Change method to send sql server sproc XML

Also, if someone could perhaps make more clear to me, the benefits one can gain from using an xsd document in addition to using XML with SQL Server 2005. From what I understand, the xsd resides on the server and I use it to validate the xml. But how else can the xsd benefit me in terms of performance?

Re: Change method to send sql server sproc XML

Not sure about 2005, But XSDs can be used to exuecute queires on the SQL Database. So they can be used as alternatives to direct SQL Queries or SQL Queries in stored procedures.

The advantage is similar to SPs, where in we can jsut modify the XSD if we need to modify the data that is returned instead of changing your whole code.