SET @sql = CONVERT(NVARCHAR(MAX),
@parameter.query('for $i in (/DuplicateRecordForGC) return concat("(Amount[.=",
data($i/Amount[1]),
"] and OrderNumber[.=""",
data($i/OrderNumber[1]),
"""] and AmountType[.=""",
data($i/AmountType[1]),
"""]) or")')
)
SET @sql = '['+SUBSTRING(@sql,0,LEN(@sql)-2)+']'
SET @sql = 'set @XML.modify(''delete /ReportObjectModel/DataRecord'+@sql+''')'
EXEC sp_executesql @sql, N'@XML xml output', @XML output
SELECT Node.value('(OrderNumber)[1]', 'VARCHAR(20)') AS OrderNumber,
Node.value('(Amount)[1]', 'DECIMAL(18,2)') AS Amount,
Node.value('(AmountType)[1]', 'VARCHAR(10)') AS AmountType
FROM @XML.nodes('ReportObjectModel/DataRecord') TempXML(Node)