Click here to Skip to main content
13,764,990 members
Rate this:
Please Sign up or sign in to 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 :
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 4:55am
Updated 26-Mar-13 4:56am
Shanalal Kasim 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.181114.1 | Last Updated 29 Mar 2013
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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