Regarding EXEC sp_xml_preparedocument exception in SQL 2012

Question

Hi,

 I'm using this proc (sp_xml_preparedocument) to parse/read xml file as input in the below stmts.

SELECT @d ='<ROOT>' +BulkColumn + '</ROOT>' FROM OPENROWSET( BULK 'D:\temp\report.txt',SINGLE_CLOB) as a

EXEC sp_xml_preparedocument @id OUTPUT, @d

This is working perfectly on SQL Server 2008, but failing on SQL Server 2012 with below exception. 

Warning: Null value is eliminated by an aggregate or other SET operation.
The XML parse error 0xc00ce501 occurred on line number 3, near the XML text "efresh="2012-02-22".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is 'Missing equals sign between attribute and attribute value.'.
Msg 8179, Level 16, State 5, Line 16
Could not find prepared statement with handle 0.
The statement has been terminated.
Msg 6607, Level 16, State 3, Procedure sp_xml_removedocument, Line 1
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

Appreciate if any suggestions on this.

Thanks,

Answer 1

Can you put output of @d near the efresh="2012-02-22"

?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi Naomi,

Here is the sample,

....

26T12:00:01.3791799" UTCTime="2012-04-26T19:00:01.3791799"/><row PhysicalPartition="11" Avg30MinLatency_P="7" Avg90MinLatency_P="7" Avg24HrsLatency_P="7" AvgLatency30Min_S="7" AvgLatency90Min_S="7" AvgLatency24Hrs_S="7" MaxLatency30Min_S="11" MaxLatency90Min_S="11" MaxLatency24Hrs_S="11" ODSLastR
efresh="2012-04-26T18:59:46.250" LocalTime="2012-04-26T12:00:01.3791799" UTCTime="2012-04-26T19:00:01.3791799"/><row PhysicalPartition="12" Avg30MinLatency_P="7" ....

Thanks,

Answer 3

I think you need to search for this particular value as the error seems to hint that the error is in that portion of XML file.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

But, the same input file doesn't cause any issues when ran in SQL Server 2008.

Thanks,

Answer 5

True, but perhaps there is a problem in XML which SQL Server 2008 can overcome, but SQL Server 2012 can not. It can also be a bug in SQL Server 2012 (BTW, there is a CU which fixes many problems).

I think you need to start from finding the problematic text in that file and try to examine it if anything is wrong.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

It looks like the problem is with return character in the input file,

OLD Exception raised at:

MaxLatency30Min_S="11" MaxLatency90Min_S="11" MaxLatency24Hrs_S="11" ODSLastR
efresh="2012-04-26T18:59:46.250" LocalTime="2012-04-26T12:00:01.3791799" UTCTime="2012-04-26T19:00:01.3791799"/><row 

Changed input file to below:

MaxLatency30Min_S="11" MaxLatency90Min_S="11" MaxLatency24Hrs_S="11" ODSLastRefresh="2012-04-26T18:59:46.250" LocalTime="2012-04-26T12:00:01.3791799" UTCTime="2012-04-26T19:00:01.3791799"/><row 

Now the exception has transferred to another line in the input file, 

AvgLatency90Min_S="7" AvgLatency24Hrs_S="7" MaxLatency30Min_
S="11" MaxLatency90Min_S="11" MaxLatency24Hrs_S="11" ODSLastRefresh="2012-04-26T19:29:10.713" LocalTime="2012-04-

Thanks,

Answer 7

I see. So, SQL 2012 seems to be more strict here. Can you remove all carriage returns from that file and re-try? 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 8

please refer the given link, it may be helpful for you.

http://searchsqlserver.techtarget.com/tip/Processing-XML-files-with-SQL-Server-functions