Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.