[Resolved]The XML parse error near XML text | Msg 6602 Procedure sp_xml_preparedocument, Line 1 The error description is 'Whitespace is not allowed at this location.' | Msg 8179 Could not find prepared statement with handle 0.

hi in this post i show how to resolve the error generated while XML Parsing in SQL.

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 &amp; 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 &amp; 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