InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 00:51:13 GMT20http://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxThanks so much, I removed the spaces and corrected the syntax. It works perfectly. :-)Tue, 06 Nov 2012 05:12:42 GMTTeeehttp://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxAlso you cannot have spaces in element names, <Data Extract> needs to be <DataExtract>[code="sql"]DECLARE @SQL NVARCHAR(MAX) = '<DataExtract><Graduates><Candidate><FirstName>Shaun</FirstName><LastName>Slabber</LastName></Candidate><Candidate><FirstName>paul</FirstName><LastName>devine</LastName></Candidate></Graduates><ExperiencedHires><Candidate><FirstName>paul</FirstName><LastName>devine</LastName></Candidate><Candidate><FirstName>Meera</FirstName><LastName>Bhana</LastName></Candidate></ExperiencedHires></DataExtract>'select @SQLselect CAST(@sql as xml)[/code]Tue, 06 Nov 2012 04:42:35 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxYour end tag is wrong'/<Data Extract>'should be'</Data Extract>'Tue, 06 Nov 2012 04:35:46 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxhere's the output:<Data Extract><Graduates><Candidate><FirstName>Shaun</FirstName><LastName>Slabber</LastName></Candidate><Candidate><FirstName>paul</FirstName><LastName>devine</LastName></Candidate></Graduates><ExperiencedHires><Candidate><FirstName>paul</FirstName><LastName>devine</LastName></Candidate><Candidate><FirstName>Meera</FirstName><LastName>Bhana</LastName></Candidate></ExperiencedHires>/<Data Extract>Tue, 06 Nov 2012 04:24:56 GMTTeeehttp://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxWhat is the output of @SQL before you convert it to XML?[code="sql"]DECLARE @sql Nvarchar(max)SET @sql = '<Data Extract>'SET @sql = @sql + (SELECT Top 2 FirstName, LastNameFROM[dbo].[CandPersonal] peINNER JOIN CandProfile pr on pe.CandId = pr.IdWHERE pr.isGraduateApplicant = '1'FOR XML PATH('Candidate'), ROOT('Graduates'))SET @sql = @sql + (SELECT Top 2 FirstName, LastNameFROM[dbo].[CandPersonal] peINNER JOIN CandProfile pr on pe.CandId = pr.IdWHERE pr.isGraduateApplicant = '0'FOR XML PATH('Candidate'), ROOT('ExperiencedHires'))SET @sql = @sql + '/<Data Extract>'SELECT @SQL[/code]Tue, 06 Nov 2012 04:14:22 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1381481-392-1.aspxHi all, I'm trying to convert a string to XML, I'm not sure why I'm getting the error:XML parsing: line 1, character 14, equal expectedWhen I run the stored proc below to select the XML data. Please helpKind RegardsTeeeCREATE PROCEDURE TestASBEGIN DECLARE @sql Nvarchar(max) SET @sql = '<Data Extract>' SET @sql = @sql + (SELECT Top 2 FirstName, LastName FROM [dbo].[CandPersonal] pe INNER JOIN CandProfile pr on pe.CandId = pr.Id WHERE pr.isGraduateApplicant = '1' FOR XML PATH('Candidate'), ROOT('Graduates')) SET @sql = @sql + (SELECT Top 2 FirstName, LastName FROM [dbo].[CandPersonal] pe INNER JOIN CandProfile pr on pe.CandId = pr.Id WHERE pr.isGraduateApplicant = '0' FOR XML PATH('Candidate'), ROOT('ExperiencedHires')) SET @sql = @sql + '/<Data Extract>' SELECT CAST(@sql as XML) --XML parsing: line 1, character 14, equal expectedENDTue, 06 Nov 2012 04:08:18 GMTTeee