Microsoft KB Archive/279434

= PRB: Limitations of OpenXML in SQL Server 2000 =

Article ID: 279434

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q279434



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.



CAUSE
This is a limitation of SQL Server 2000.



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



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 =' This is a test ' 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

Keywords: kbprb KB279434

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.