Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: XML SQL SQL-Server
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 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>'
Posted 26-Mar-13 4:55am
Edited 26-Mar-13 4:56am
ProgramFOX127.4K
v3
Comments
Shanalal Kasim at 27-Mar-13 0:21am
   
provide error text

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
v5

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

  Print Answers RSS
0 Mathew Soji 330
1 BillWoodruff 260
2 Sergey Alexandrovich Kryukov 240
3 OriginalGriff 216
4 Afzaal Ahmad Zeeshan 208
0 OriginalGriff 6,168
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,028
3 Manas Bhardwaj 4,539
4 Maciej Los 3,845


Advertise | Privacy | Mobile
Web04 | 2.8.1411019.1 | Last Updated 29 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100