How are you being given the XML? If it is in a file, you can import the data into a table even if it has single quotes, as long as the XML is well-formed (which it is, for the XML fragment you have shown). Presence of single quote would not be a problem then. For example:CREATE TABLE #tmp(xmlCol XML);INSERT INTO #tmpSELECT * FROM OPENROWSET( BULK 'c:\YourXmlFile.txt', SINGLE_BLOB) AS x;SELECTBatch.client.value('./@dealerid', 'nvarchar(max)') as DID,Batch.client.value('./@OpportunityID', 'nvarchar(max)') as OpportunityID,Batch.client.value('./@ClientName', 'nvarchar(max)') as Clientname,Batch.client.value('./@saleid', 'nvarchar(max)') as SaleID,Batch.client.value('./@email', 'nvarchar(max)') as EmailFROM #tmp cross apply xmlCol.nodes('/EmailBatch/EmailDetail') Batch(client) If you are getting the data from the client and pasting it to somewhere, then you can escape the single quotes by inserting another single quote:DECLARE @TEMPXML XMLSET @TEMPXML = '<EmailBatch><EmailDetail dealerid="51" OpportunityID="74892" ClientName="Martha''s Hart" saleid="255049" email="JP@YAHOO.COM" /><EmailDetail dealerid="51" OpportunityID="98802" ClientName="Timothy''s Lowry" saleid="255273" email="karen@hinds.com" /></EmailBatch> 'SELECTBatch.client.value('./@dealerid', 'nvarchar(max)') as DID,Batch.client.value('./@OpportunityID', 'nvarchar(max)') as OpportunityID,Batch.client.value('./@ClientName', 'nvarchar(max)') as Clientname,Batch.client.value('./@saleid', 'nvarchar(max)') as SaleID,Batch.client.value('./@email', 'nvarchar(max)') as EmailFROM @TEMPXML.nodes('/EmailBatch/EmailDetail') Batch(client) 
single quotes( ' ) issue in XML
Posted - 2012-03-15 : 03:47:33
