Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/forums/sql-server-forum/)
-   -   Change method to send sql server sproc XML (http://www.go4expert.com/forums/change-method-send-sql-server-sproc-xml-t5310/)

rhaazy 17Jul2007 19:58

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.

rhaazy 17Jul2007 23:37

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?

urstop 11Dec2007 14:19

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.


All times are GMT +5.5. The time now is 23:28.