Best approach, Dynamic OpenXML in T-SQL

Question

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.

Answer 1

Your calls to sp_xml_% (both of them) would also have to be dynamic so the handle @i is recognised

Surely this is best in your client code though?

Off topic: and a GUID PK too?