Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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 :
SQL
set @Lead= (select lead from openxml(@DOCHANDLE,'/DBO.TBLLEADS',2) with (lead INT 'LEAD'))

XML here:
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>'
Posted
Updated 26-Mar-13 3:56am
v3
Comments
Shanalal Kasim 27-Mar-13 0:21am    
provide error text

1 solution

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

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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900