<?xml version = "1.0" encoding="Windows-1252" standalone="yes"?> <VFPData> <tmp1> <pointcode>HKHKG</pointcode> <visitno>2.0</visitno> <eta>2004-05-23T00:00:00</eta> <etd>2004-05-23T00:00:00</etd> </tmp1> <tmp1> <pointcode>THLCH</pointcode> <visitno>1.0</visitno> <eta>2004-05-16T00:00:00</eta> <etd>2004-05-17T00:00:00</etd> </tmp1> <tmp1> <pointcode>SGSIN</pointcode> <visitno>2.0</visitno> <eta>2004-05-19T00:00:00</eta> <etd>2004-05-19T00:00:00</etd> </tmp1> </VFPData>SQL SP:
CREATE PROCEDURE ReadXML ( @xml text ) as declare @xdoc int exec sp_xml_preparedocument @xdoc output, @xml --select @xml select t.* from openxml (@xdoc, '/VFPData/tmp1',2 ) with (pointcode char(5),visitno numeric(5,1),ETA datetime,ETD datetime) as t exec sp_xml_removedocument @xdoc GO