hi in this post i show how to resolve the error generated while XML Parsing in SQL.
Example :
After Executing this above query, we get the error as below:
The XML parse error 0xc00ce513 occurred on line number 2, near the XML text "<EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/>".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Whitespace is not allowed at this location.'.
Msg 8179, Level 16, State 5, Line 9
Could not find prepared statement with handle 0.
The problem is due to the using of ampersand(&) in the xml document.
Solution:
While creating the XML document replace the ampersand(&) with & and then re-run the query to get the correct output.
Example :
DECLARE @INDEX INT DECLARE @XMLEMPLOYEE NVARCHAR(MAX) SET @XMLEMPLOYEE = N'<ROOT><EMPLOYEE EMPID="1" EMPNAME="CHANDAN"/> <EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/> </ROOT>' EXEC SP_XML_PREPAREDOCUMENT @INDEX OUTPUT ,@XMLEMPLOYEE SELECT EMPID ,EMPNAME FROM OPENXML(@INDEX, 'ROOT/EMPLOYEE') WITH ( EMPID NVARCHAR(50) ,EMPNAME NVARCHAR(550) )
After Executing this above query, we get the error as below:
The XML parse error 0xc00ce513 occurred on line number 2, near the XML text "<EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/>".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Whitespace is not allowed at this location.'.
Msg 8179, Level 16, State 5, Line 9
Could not find prepared statement with handle 0.
The problem is due to the using of ampersand(&) in the xml document.
Solution:
While creating the XML document replace the ampersand(&) with & and then re-run the query to get the correct output.
DECLARE @INDEX INT DECLARE @XMLEMPLOYEE NVARCHAR(MAX) SET @XMLEMPLOYEE = N'<ROOT><EMPLOYEE EMPID="1" EMPNAME="CHANDAN"/> <EMPLOYEE EMPID="2" EMPNAME="CHANDAN & SINGH"/> </ROOT>' EXEC SP_XML_PREPAREDOCUMENT @INDEX OUTPUT ,@XMLEMPLOYEE SELECT EMPID ,EMPNAME FROM OPENXML(@INDEX, 'ROOT/EMPLOYEE') WITH ( EMPID NVARCHAR(50) ,EMPNAME NVARCHAR(550) )
It will give the ouput as :
EMPID EMPNAME
1 CHANDAN
2 CHANDAN & SINGH
Thanks Sir.. Issue Resolved
ReplyDelete