SYMPTOMS
Streaming the results of an OpenXML command into other SQL commands fails with the following error message:
OLE DB provider 'OpenXML' reported an error. The provider did not give any information about the error.
STATUS
SQL Server 2000 currently has a limitation on piping an intermediate resultset from one command to another when OpenXml is used in one of the sub-commands.
The problem is exhibited when running a command that would require linking the intermediate resultset from a sub-SELECT or JOIN. The following T-SQL sample demonstrates the failure:
DECLARE @hXMLInsertDoc int, @hXMLInsertDocText varchar(1000)
SET @hXMLInsertDocText ='<Root><test>This is a test</test></Root>'
EXEC sp_xml_preparedocument @hXMLInsertDoc OUTPUT, @hXMLInsertDocText
SELECT * FROM
(SELECT *
FROM OPENXML (@hXMLInsertDoc, '/Root/*', 0)) AS A
INNER JOIN
(SELECT *
FROM OPENXML (@hXMLInsertDoc, '/Root/*', 0)) AS B ON
A.ID=B.Parentid
To work around the problem, use temporary tables to store the results of the OpenXML, and use the data in the temporary tables instead. For example, instead of issuing the preceding SELECT statement, execute the following statements:
SELECT * into #OpenXMLResults FROM OPENXML (@hXMLInsertDoc, '/Root/*', 0)
SELECT * from #OpenXMLResults A inner join #OpenXMLResults B on B.parentid=a.id