Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: need help on Stored Procedure using XML related functions

    Hi all,

    I came across the below SQLServer stored procedure, I understand that it manipulates some XML data, but am not sure what it does.. Could any one pls explain me on the below procedure,

    Also, i do not see 'values' clause in the insert statement in the below procedure, is it valid to use in this way? (as per the syntax, 'values' clause is NOT optional)


    Code:
    CREATE PROCEDURE [dbo].[OM_addObjData] 
        @xml_Doc_sample varchar(max),
    	@objID int
    
    AS
    BEGIN
    
    	--set @xml_Doc_sample = '<?xml version="1.0" encoding="utf-8"?><ArrayOfEXEData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><EXEData><EXEName>exeName</EXEName><EXECommand>exeCommand</EXECommand></EXEData></ArrayOfEXEData>'
    
    	declare @docIdx int
    	EXECUTE sp_xml_preparedocument @docIdx OUTPUT, @xml_Doc_sample
    
    
    		insert into OM_EXE
    		select @objID, exeName, exeCommand, IdEXE, EXE
    		From OPENXML(@docIdx, N'/ArrayOfEXEData/EXEData', 2) With OM_EXE
    
    
    	EXECUTE sp_xml_removedocument @docIdx 
    
    END

    Thanks in Advance,
    Shefali Mihir
    Last edited by Shefu; 04-20-07 at 02:03.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not up on xml, so I'll leave that for someone else, but I can confirm that the SELECT clause is perfectly valid. You can insert 1 record with a VALUES clause, 1 or more with a SELECT clause (however many are returned by it).
    Paul

  3. #3
    Join Date
    Nov 2005
    Posts
    122
    It's perfercly legal to specify a select statement with INSERT instead of VALUES.

    This sproc transforms the values of EXEName and EXECommand elements (if table OM_EXE contains column named EXEName and EXECommand) for each EXEData elements into individual rows, and inserts them into the OM_EXE table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •