Click here to Skip to main content
11,789,710 members (62,471 online)
Rate this: bad
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"?>
Posted 26-Mar-13 3:55am
Edited 26-Mar-13 3:56am
Shanalal Kasim at 27-Mar-13 0:21am
provide error text

1 solution

Rate this: bad
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>'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
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.

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

  Print Answers RSS
0 OriginalGriff 999
1 Maciej Los 765
2 KrunalRohit 686
3 CPallini 606
4 Richard MacCutchan 430

Advertise | Privacy | Mobile
Web03 | 2.8.1509028.1 | Last Updated 29 Mar 2013
Copyright © CodeProject, 1999-2015
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