Is using OPENXML() the best way to transform data in an XML Column to Rows and Columns?

Question

I have a SQL Server table with a column of type XML. The XML data represents multiple allowable selections for a given field. An example of the XML data is as follows.

<Values>
    <Value>Valid Value 1</Value>
    <Value>Valid Value 2</Value>
    <Value>Valid Value 3</Value>
    <Value>Valid Value 4</Value>
    <Value>Valid Value 5</Value>
    <Value>Valid Value 6</Value>
    ...
</Values>

I am using the following code to extract the data from the XML column and transforming it into rows that can be inserted into a new table.

DECLARE @XmlStuff VARCHAR(4000);
DECLARE @iXmlStuff int;

SELECT @XmlStuff = CAST(C.ValidValues AS VARCHAR(4000))
FROM dbo.ColumnValidations C
WHERE C.[ColumnName] = 'Something';

EXEC sp_xml_preparedocument @iXmlStuff OUTPUT, @XmlStuff;

SELECT *
FROM OPENXML(@iXmlStuff, '/Values/Value', 2)
WITH ([Value] VARCHAR(100) '.');

EXEC sp_xml_removedocument @iXmlStuff;

This code is correctly returning the following

Value
----------------
Valid Value 1
Valid Value 2
Valid Value 3
Valid Value 4
Valid Value 5
Valid Value 6
...

Is this the best way of doing this?

What I have here, I think, will need to be in a stored procedure. Ideally I am looking for a way of doing this where I don't have to worry about losing data because of a buffer overflow due to an unforeseen quantity of data contained in the xml column.

Answer 1

OPENXML(), and its companions sp_xml_preparedocument/sp_xml_removedocument is a proprietary Microsoft API. It is kept just for backward compatibility with the obsolete SQL Server 2000. SQL Server 2005 onwards supports w3c's XQuery 1.0, XPath 2.0, and XSD 1.0.

SQL

-- DDL and sample data population, start
DECLARE @tbl Table (ID INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<Values>
    <Value>Valid Value 1</Value>
    <Value>Valid Value 2</Value>
    <Value>Valid Value 3</Value>
    <Value>Valid Value 4</Value>
    <Value>Valid Value 5</Value>
    <Value>Valid Value 6</Value>
</Values>');
-- DDL and sample data population, end

SELECT ID
    , c.value('.','VARCHAR(100)') AS Result
FROM @tbl CROSS APPLY xmldata.nodes('/Values/Value/text()') AS t(c);

Output

+----+---------------+
| ID |    Result     |
+----+---------------+
|  1 | Valid Value 1 |
|  1 | Valid Value 2 |
|  1 | Valid Value 3 |
|  1 | Valid Value 4 |
|  1 | Valid Value 5 |
|  1 | Valid Value 6 |
+----+---------------+