使用OPENXML()是最好的方式来转换数据在XML列行和列?

Question

我有一个列类型为XML的SQL Server表。XML数据表示给定字段的多个允许选择。XML数据的示例如下:

<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>

我使用以下代码从XML列中提取数据，并将其转换为可以插入到新表中的行。

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;

这段代码正确地返回了以下

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

这是最好的方法吗?

我认为，这里的需要放在存储过程中。理想情况下，我正在寻找这样做的一种方式，我不必担心丢失数据，因为缓冲区溢出由于在xml列中包含的不可预见的数据量。

Answer 1

OPENXML()及其同伴sp_xml_preparedocument/sp_xml_removedocument是微软专有的API。它是专门用来保存的与过时的SQL Server 2000的兼容性。SQL Server 2005以后支持w3c的XQuery 1.0、XPath 2.0和XSD 1.0。

/p>

-- 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);

+----+---------------+
| 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 |
+----+---------------+