select * from openxml (@xdoc, '/VFPData/tmp1',2 ) with (pointcode char(5),visitno numeric(5,1), ETA datetime 'eta',ETD datetime 'etd') -- or select * from openxml (@xdoc, '/VFPData/tmp1',2 ) with (pointcode char(5),visitno numeric(5,1), eta datetime ,etd datetime )>> When i use openxml() sparsing the XML string which is generated with cursortoxml() in VFP9 into a table , all numeric &
> <?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 > >