Sql 操作 XML

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 AmountTypeFROM @XML.nodes('ReportObjectModel/DataRecord') TempXML(Node)