How to get XML element/attribute name in SQL Server 2005
- by OG Dude
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
