How to extract data from XML: Error A string literal was expected

Question

Below are my code. While running below code i am getting error

Msg 9413, Level 16, State 1, Line 2
XML parsing: line 1, character 33, A string literal was expected

if object_id('tempdb..##DataSource') is not null
begin
    drop table ##DataSource
end

CREATE TABLE ##DataSource
(
    ParameterValue bigint,
    Source nvarchar(100)
)
 
DECLARE  @XMLStringDataSource XML  = '<ROOT><data><row ParameterValue=\"12345678\" Source=\"Intake\"/><row ParameterValue=\"44444444\" Source=\"Intake\"/> </data></ROOT>'

INSERT INTO ##DataSource (ParameterValue,Source)
SELECT 
ParameterValue = XTBl.XCol.value('@ParameterValue','bigint') ,
Source = XTBl.XCol.value('@Source','nvarchar(100)')
FROM  @XMLStringDataSource.nodes('ROOT/data/row')  XTbl(XCol)

Answer 1

The error is thrown because of the escaped double quotes (\") in the XML-Document. Just remove the backslash \ and the script is working.

if object_id('tempdb..##DataSource') is not null
begin
    drop table ##DataSource
end

CREATE TABLE ##DataSource
(
    ParameterValue bigint,
    Source nvarchar(100)
)
 
DECLARE  @XMLStringDataSource XML  = '<ROOT><data><row ParameterValue="12345678" Source="Intake"/><row ParameterValue="44444444" Source="Intake"/> </data></ROOT>'

INSERT INTO ##DataSource (ParameterValue,Source)
SELECT 
ParameterValue = XTBl.XCol.value('@ParameterValue','bigint') ,
Source = XTBl.XCol.value('@Source','nvarchar(100)')
FROM  @XMLStringDataSource.nodes('ROOT/data/row')  XTbl(XCol)

If you want to use double quotes inside a parameter value, you have to escape them with ". Also have a look at Escape double quote character in XML for more information about escaping special characters in XML.