DECLARE @idoc int
DECLARE @doc varchar(8000)
SET @doc ='
REF-123
SGH1234567
REF-124
SGH1234568
ACKNOWLEDGEC
2011-04-12
REF-128
SGH1234577
REF-129
SGH1234578
ACKNOWLEDGEF
2011-04-18
'
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT *FROM OPENXML (@idoc, '/ROOT/MaerskXML/MaerskInfo')WITH (
value varchar(20) '@value',
ShippingOrderREF varchar(20) 'ShippingOrderREF',
DamcoSONumber varchar(20) 'DamcoSONumber',
[ACTION] varchar(20) '../ACTION',
opDate varchar(20) '../opDate')
EXEC sp_xml_removedocument @idoc