1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Change method to send sql server sproc XML

Discussion in 'SQL Server' started by rhaazy, Jul 17, 2007.

  1. rhaazy

    rhaazy New Member

    Joined:
    Jul 6, 2007
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    0
    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.
     
  2. rhaazy

    rhaazy New Member

    Joined:
    Jul 6, 2007
    Messages:
    28
    Likes Received:
    0
    Trophy Points:
    0
    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?
     
  3. urstop

    urstop New Member

    Joined:
    Oct 17, 2007
    Messages:
    84
    Likes Received:
    0
    Trophy Points:
    0
    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.
     

Share This Page