Article ID: 279434 - Last Review: October 3, 2003 - Revision: 3.2

PRB: Limitations of OpenXML in SQL Server 2000

View products that this article applies to.
This article was previously published under Q279434
Expand all | Collapse all

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.
Back to the top

CAUSE

This is a limitation of SQL Server 2000.
Back to the top

RESOLUTION

Direct the output from the OpenXML into a temporary table prior to selecting it with the secondary command.
Back to the top

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
Back to the top
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Back to the top
Keywords: 
kbprb KB279434
Back to the top
 

Get Help Now

Contact a support professional by E-mail, Online, or Phone

Article Translations

 

Related Support Centers