Hi,

I have a simple procedure in SQL Server 2005 which takes some XML as input. The element attributes correspond to field names in tables. I'd like to be able to determine <elementName>, <attribNameX> dynamically as to avoid having to hardcode them into the procedure. How can I do this?

The XML looks like this:

<ROOT> <elementName attribName1 = "xxx" attribName2 = "yyy"/> <elementName attribName1 = "aaa" attribName2 = "bbb"/> ... </ROOT>

The stored procedure like this:

CREATE PROC dbo.myProc
(
    @XMLInput varchar(1000)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @XMLDocHandle int

    EXEC sp_xml_preparedocument @XMLDocHandle OUTPUT, @XMLInput 

    SELECT someTable.someCol
    FROM    dbo.someTable
        JOIN
        OPENXML (@XMLDocHandle, '/ROOT/elementName',1) WITH (attrib1Name int, attrib2Name int) AS XMLData
        ON someTable.attribName1 = XMLData.attribName1
           AND someTable.attribName2 = XMLData.attribName2
    EXEC sp_xml_removedocument @XMLDocHandle 
END
GO

The question has been asked here before but maybe there is a cleaner solution.

Additionally, I'd like to pass the tablename as a parameter as well - I read some stuff arguing that this is bad style - so what would be a good solution for having a dynamic tablename?

Thanks a lot in advance,

/David