sql存储过程中的XML解析错误 [英] XML parsing error in sql stored proc

问题描述

我想在sql server存储过程中解析下面的XML，并根据这个XML更新一些表。我已经使用OPENXML实现了相同的功能，但现在在XML的开头添加了一行，因为它会出现意外错误。解析时是否有可能以某种方式单独跳过第一个标签



解析代码：

I want to parse the below XML in sql server stored procedure and update some tables based on this XML. I have implemented the same using OPENXML but now there is one more line added to the beginning of the XML, because of which am getting unexpected errors. Is it possible to somehow skip the first tag alone while parsing

Parsing code :

set @Lead= (select lead from openxml(@DOCHANDLE,'/DBO.TBLLEADS',2) with (lead INT 'LEAD'))

这里的XML：

XML here:

'<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<LEADS>
<LEAD>6680299</LEAD>
<JOBNO>50919</JOBNO>
<BEGINDATE>4-04-2013</BEGINDATE>
<ENDDATE>04/14/2013</ENDDATE>
</LEADS>'

推荐答案

保存错误文本;也许这就是它（或类似的东西）：

Save your error text; perhaps this is it (or something like it):

The XML parse error 0xc00ce56f occurred on line number 1, near the XML text "".
Msg 6602, Level 16, State 2, Procedure sp_xml_preparedocument, Line 1
The error description is ''Switch from current encoding to specified encoding not supported.''.
Msg 8179, Level 16, State 5, Line 13
Could not find prepared statement with handle 0.

在哪种情况下，做这个（或类似的东西）：

In which case, do this (or something like this):

DECLARE @hdoc int
DECLARE @xml [nvarchar](MAX)
SET @xml = ''SET @xml = ''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
			<LEADS>
				<LEAD>6680299</LEAD>
				<JOBNO>50919</JOBNO>
				<BEGINDATE>4-04-2013</BEGINDATE>
				<ENDDATE>04/14/2013</ENDDATE>
			</LEADS>''DECLARE @xmlR [nvarchar](MAX)		
SET @xmlR = REPLACE(@xml,''<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'','''')			

---EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xmlR

SELECT * FROM OPENXML(@hdoc,''LEADS/'',2)

结果是一个边缘表。这是：

The results is an "edge table". Which is this:

id   parentid   nodetype   localname   prefix   namespaceuri   datatype   prev   text
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0    NULL	     1          LEADS       NULL     NULL           NULL       NULL   NULL
2    0	     1          LEAD        NULL     NULL           NULL       NULL   NULL
6    2          3          #text       NULL     NULL           NULL       NULL   6680299
3    0          1          JOBNO       NULL     NULL           NULL       2      NULL
7    3          3          #text       NULL     NULL           NULL       NULL   50919
4    0          1          BEGINDATE   NULL     NULL           NULL       3      NULL
8    4          3          #text       NULL     NULL           NULL       NULL   4-04-2013
5    0          1          ENDDATE     NULL     NULL           NULL       4      NULL
9    5          3          #text       NULL     NULL           NULL       NULL   04/14/2013

但是这不应该阻止你使用WHERE子句限定返回数据。

But that shouldn''t deter you from qualifying the return feild using a WHERE clause.

这篇关于sql存储过程中的XML解析错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！