I have a table that stores a name | value pair, which I'm trying to load from a column stored as XML. The only way that I have found to shred the id and value from the XML document is to create a stored procedure which I would need to call for each record.
Does anyone have any good recommendations for turning this operation into a set-based solution?
My sample looks like this ...
DECLARE @docHandle INT; DECLARE @xmlDocument NVARCHAR(500);
-- XML value is stored in a table SET @xmlDocument = ' ';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
SELECT [id],[value] FROM OPENXML(@docHandle,N'/attributes/attribute') WITH (id INT, value NVARCHAR(500));
EXEC sp_xml_removedocument @docHandle; GO
Returns ...
[id], [value] 1, 1
Any ideas??
Sean Fitzgerald
