CODEWITHSUNDEEP
sqlsql-serverxmlxml-parsing
Bharath
Bharath

Reputation: 581

XML parsing error in SQL Server stored procedure

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>

Upvotes: 0

Views: 3547

Answers (1)

Rick Todd
Rick Todd

Reputation: 21

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.

Upvotes: 1

Related Questions