Universal storage to import all XML documents in the specified directory
CREATE PROC P_INPORT @ path nvarchar (1000) = 'd: / xml', the directory where the system is located @fname nvarchar (1000) = '- The XML file name to import, if not specified, indicating that only import today If:%, import all the files in this directory, if so, it means only the specified asdeclare @idoc int, @ DOC VARCHAR (8000)
IF right (@ PATH, 1) <> '/' set @ path = @ Path '/' if @ fname = '' set @ fname = convert (char (8), getdate (), 112) '. xml'
- Get all files in the specified directory: Create Table #t (FName Varchar (260), Depth Int, ISF Bit) Insert Into #t exec master..xp_dirtree @ Path, 1, 1
- Define Procedure Document Declare Tb Cursor Local for SELECT FN = @ Path FName from #twhere ISF = 1 And Fname Like @fname
- Importing Declare @fn varchar (2000) Create Table #TB (DOC VARCHAR (8000)) open tbfetch next from tb @fnwhile @@ fetch_status = 0begin EXEC ('bulk insert #stb from' '' @fn ' '') Set @ DOC = 'SELECT @ DOC = @ DOC DOC FROM #TB
- Analysis of XML, the import table EXEC sp_xml_preparedocument @idoc OUTPUT, @doc insert table1 SELECT * FROM OPENXML (@idoc, '/ Database / Vehicle', 1) with table1 insert into table2 SELECT * FROM OPENXML (@idoc, '/ Database / Vehicle / Pictures / Picture ', 1) with (ExamineNumber Char (30)' / Database / Vehicle / @ ExamineNumber ', PictureFileName Char (50)' @PictureFileName ') insert into table3 SELECT * FROM OPENXML (@idoc,' / DATABASE / VEHICLE / Drivers / Driver ', 1) with (Examinenumber Char (30)' / Database / Vehicle / @ Examinenumber ', Drivername Char (10), Idcard Char (20), Tel Char (50)' @tel ' , MEMO CHAR (200) '@MEMO')
EXEC SP_XML_REMOVEDOCUMENT @IDOC
