pcs at azizaz.com
pcs at azizaz.com
Wed Oct 17 23:11:08 CDT 2007
John, I don't know if this would be of any help to you. The Transact-SQL keyword named OPENXML along with two supporting system stored procedures: sp_xml_preparedocument and sp_xml_removedocument may be what you need to do the job. Here is a pointer to an article: http://www.perfectxml.com/articles/xml/openxml.asp Using some of the article samples and slightly modifying your xml file I created this SP below. Try and run it.. You can replace the Select with an Insert etc... If you know how to reference the xml file instead of having the text hardcoded let me know .... Regards borge **************** DECLARE @idoc int DECLARE @doc varchar (1000) SET @doc ='<clsLogData> <pTblName>AK <pStatus>Complete</pStatus> <pCommittedID>205453</pCommittedID> <pFileSpec>\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.tx t</pFileSpec> <pStartTime>2007-10-03T12:55:49.890375-04:00</pStartTime> <pStopTime>2007-10-03T12:57:15.49325-04:00</pStopTime> <pStartID>0</pStartID> <pStopID>205453</pStopID> <pMemo>90071218 : 10/3/2007 12:55:49 PM : Read Directory: TaxRolls 90071234 : </pMemo> </pTblName> </clsLogData>' --Create an internal representation of the XML document EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement that uses the OPENXML rowset provider SELECT * FROM OPENXML (@idoc, '/clsLogData/pTblName',3) WITH ( pStatus varchar(80), pCommittedID int, pFileSpec varchar(255), pStartTime varchar(255), pStopTime varchar(255), pStartID int, pStopID int, pMemo varchar(max) ) -- Clear the XML document from memory EXEC sp_xml_removedocument @idoc ********************* ---- Original message ---- >Date: Wed, 17 Oct 2007 12:03:43 -0700 >From: "Eric Barro" <ebarro at verizon.net> >Subject: Re: [dba-SQLServer] Importing XML into a database >To: <dba-sqlserver at databaseadvisors.com> > >John, > >I'll have to dig into my code library but generally you convert the XML to a >dataset and then you can write the dataset to a SQL table. > >Eric > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby >Sent: Friday, October 05, 2007 9:53 AM >To: 'Access Developers discussion and problem solving'; >dba-sqlserver at databaseadvisors.com >Subject: [dba-SQLServer] Importing XML into a database > >I have found / written code in VB.Net to allow a class to write (and read) >it's data out to a text file on disk. I use this ATM to log my import >process. When I am done I have a log file per file imported. I will >eventually be switching to a direct write to a data table but for now I need >to know if I can import these log files into a table. The XML produced by >the code looks like the following (actual file written): > ><?xml version="1.0"?> ><clsLogData xmlns:xsi="http://www.w3.org/2001/XMLSchema- instance" >xmlns:xsd="http://www.w3.org/2001/XMLSchema"> > <pTblName>AK</pTblName> > <pStatus>Complete</pStatus> > <pCommittedID>205453</pCommittedID> > <pFileSpec>\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.tx t</pFileSpec> > <pStartTime>2007-10-03T12:55:49.890375-04:00</pStartTime> > <pStopTime>2007-10-03T12:57:15.49325-04:00</pStopTime> > <pStartID>0</pStartID> > <pStopID>205453</pStopID> > <pMemo>90071218 : 10/3/2007 12:55:49 PM : Read Directory: TaxRolls >90071234 : 10/3/2007 12:55:49 PM : Loading CSVReader10/3/2007 12:55:49 PM >90071234 : 10/3/2007 12:55:49 PM : Read File: >\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt >90071250 : 10/3/2007 12:55:49 PM : File Loaded: >\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\AK.txt >90071296 : 10/3/2007 12:55:49 PM : Bulk Copy: tblTaxRolls 90116640 : >10/3/2007 12:56:35 PM : Bulk Copy Finished: tblTaxRolls >90116656 : 10/3/2007 12:56:35 PM : RECORDS READ: 205453 >90116656 : 10/3/2007 12:56:35 PM : RECORDS BULK COPIED: 205453 >10/3/2007 12:56:35 PM : Copy Temp table AK_X to the permanent table >tblTaxRolls >10/3/2007 12:57:15 PM : Temp table copied AK_X to the permanent table >tblTaxRolls >10/3/2007 12:57:15 PM : DROPPED Temp table AK_X >90156765 : 10/3/2007 12:57:15 PM : Move to Archive: >\\Stonehenge\psm\Data\FirstAmerican\TaxRolls\Archive\AK.txt< /pMemo> ></clsLogData> > >Can SQL Server just read these files? Does anyone have any VB.Net code to >read a file such as this and append the data to a table? > >John W. Colby >Colby Consulting >www.ColbyConsulting.com > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >