OpenXML examples

Batch transactions between an application and SQL server have always been difficult, due to the lack of support for arrays (primarily as a parameter for stored procedures). The good news, is that OpenXML can be used to convert text into a dataset.

Here are some code snippets showing how to SELECT, INSERT, or DELETE data from an XML variable.
Language:
T-SQL
Keywords:
Code Snippet

DECLARE @XMLInput text = '<insert>
                            <Answers EvalID="NN" Qnum="NN" Answer="abc" />
                          </insert>'

DECLARE @hDoc int32
sp_preparedocument @hDoc, @XMLInput

SELECT *
  FROM OPENXML (@hDoc, '/insert/Answers')
  WITH ( EvalID int
       , Qnum int
       , Answer varchar(20) )

INSERT
  INTO Answers
SELECT *
  FROM OPENXML (@hDoc, '/insert/Answers')
  WITH ( EvalID int
       , Qnum int
       , Answer varchar(20) )

DELETE
  FROM Answers
 WHERE Answers.EvalID IN
     ( SELECT EvalID
         FROM OPENXML (@hDoc, '/insert/Answers')
         WITH ( EvalID int
              , Qnum int
              , Answer varchar(20) )

sp_removedocument @hDoc


Created 2012-02-11
comments powered by Disqus
Login