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

    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)

    CREATE PROCEDURE [dbo].[OM_addObjData] 
        @xml_Doc_sample varchar(max),
    	@objID int
    	--set @xml_Doc_sample = '<?xml version="1.0" encoding="utf-8"?><ArrayOfEXEData xmlns:xsi="" xmlns:xsd=""><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 

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

  2. #2
    Join Date
    May 2005
    Nevada, USA
    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).

  3. #3
    Join Date
    Nov 2005
    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