XML parsing error in SQL Server stored procedure

Question

I want to parse the below XML in a 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>

marc_s · Accepted Answer · 2013-03-26 14:06:00Z

1

Well, not the most elegant solution, but will get it back to working:

Before you prepare your XML document, run this statement on the variable containing the XML:

SET @XMLVariable = REPLACE(@XMLVariable, '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>', '')

Basically you're leveraging the REPLACE function to replace the unnecessary header string with nothing.

edited Mar 26, 2013 at 14:06

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Mar 26, 2013 at 13:59

Rick Todd

213 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bharath Over a year ago

Got this error: Argument data type xml is invalid for argument 1 of replace function.

Collectives™ on Stack Overflow

XML parsing error in SQL Server stored procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related