Best approach, Dynamic OpenXML in T-SQL
- by Martin Ongtangco
hello, i'm storing XML values to an entry in my database. Originally, i extract the xml datatype to my business logic then fill the XML data into a DataSet. I want to improve this process by loading the XML right into the T-SQL. Instead of getting the xml as string then converting it on the BL.
My issue is this: each xml entry is dynamic, meaning it can be any column created by the user. I tried using this approach, but it's giving me an error:
CREATE PROCEDURE spXMLtoDataSet
@id uniqueidentifier,
@columns varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @name varchar(300);
DECLARE @i int;
DECLARE @xmlData xml;
(SELECT @xmlData = data, @name = name FROM XmlTABLES WHERE (tableID = ISNULL(@id, tableID)));
EXEC sp_xml_preparedocument @i OUTPUT, @xmlData
DECLARE @tag varchar(1000);
SET @tag = '/NewDataSet/' + @name;
DECLARE @statement varchar(max)
SET @statement = 'SELECT * FROM OpenXML(@i, @tag, 2) WITH (' + @columns + ')';
EXEC (@statement);
EXEC sp_xml_removedocument @i
END
where i pass a dynamically written @columns.
For example:
spXMLtoDataSet 'bda32dd7-0439-4f97-bc96-50cdacbb1518', 'ID int, TypeOfAccident int, Major bit, Number_of_Persons int, Notes varchar(max)'
but it kept on throwing me this exception:
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@i".
Msg 319, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
