There is a folder which contains several different xml files.
Question
for each xml file, how can I get the contents of the xml file and then pass it to a Stored Proc?
Is this to do with a sql function that takes the file path of the xml file, i.e. openxml or something similar?
Thanks
Hi
Try this
Code Snippet
CREATE PROCEDURE [dbo].[spInsertXMLData]
(
@.strXML varchar(8000)
)
AS
DECLARE @.iDoc int
EXECUTE sp_XML_PrepareDocument @.iDoc OUTPUT, @.strXML
INSERT INTO Test (col1, col2, col3)
(SELECT * FROM OpenXML(@.iDoc, '/parentnode/childnode/,2) WITH
(col1 varchar(100),
col2 varchar(100),
col3 varchar(100)))
EXECUTE sp_XML_RemoveDocument @.iDoc
GO
This sp will accept the contents of an XML file and insert it into a table, all you need to do is to read the contents of the XML file into a string\varchar variable and pass it as a paramater to the sp.
Richard
|||Hi,
yes, This is what I am doing down the line but first I would like to know how to read all the contents of the xml file as say varchar.
Thanks
|||Yes you can..
Code Snippet
--SQL Server 2005
Declare @.Path as varchar(100);
Set @.Path = 'C:\Mani.Xml'
Create table #xmlcontent
(
Xml varchar(max)
)
Insert into #xmlcontent
Exec ('SELECT Cast(BulkColumn as Nvarchar(max)) FROM OPENROWSET(BULK ''' + @.Path + ''', SINGLE_CLOB) as D')
Select XML From #XMLContent
Code Snippet
--SQL Server 2000
Declare @.Path as varchar(100);
Set @.Path = 'C:\Mani.Xml'
Set @.Path = 'Type ' + @.Path
Create Table #XMLLines
(
Line Varchar(8000)
)
Insert Into #XMLLines
Exec xp_cmdshell @.Path
Select * from #XMLLines
|||Hi
This is how I have done it using ActiveX Scripting in a DTS package
Code Snippet
Const DB_CONNECT_STRING = "" ' SQL Connection String
Const XMLPath = "" 'Enter UNC file Path
Function Main()
RunImport
Main = DTSTaskExecResult_Success
End Function
Private Function ReadHTML(strFileName)
Dim filesys
Dim readFile
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readFile = filesys.opentextfile(strFileName, 1, False)
ReadHTML = readFile.read(8000)
readFile.Close
Set filesys = Nothing
Set readFile = Nothing
End Function
Private Sub RunImport()
Dim cnn1
Dim strExportFiles, fso, File
Dim FileContents
Set cnn1 = CreateObject("ADODB.Connection")
cnn1.Open DB_CONNECT_STRING
Set fso = CreateObject("Scripting.FileSystemObject")
Set strExportFiles = fso.GetFolder(XMLPath).Files
For Each File In strExportFiles
FileContents = ReadHTML(File)
Dim strSQL
strSQL = "spInsertXMLData @.strXML = " + "'" & FileContents & "'"
cnn1.execute (strSQL)
Next
End Sub
This will loop through a specified directory and read the contents of each file to a varchar and run the sp.
|||hi,
Using sql 2005
error is :
Cannot bulk load. The file "C:\aud_df.xml" does not exist
The file is indeed in that location.
Thanks
|||this seems to work on the server but not the local machine.?
Do you know why?
|||Yes. It will read the data from client only.
Try with Server UNC path.. \\servername\folder\file.xml
|||Hi
I think this is because it is looking for the file in a differnet location if you use a UNC path it should be fine i.e. \\sql\xml files\xmlfile1.xml
Richard
|||What do you mean by UNC please?
Please note the files are located on my local machines and I am running the sql query analyser from my local machine too.
Thanks
|||Hi
You are running sql query analyser from your local machine, but I am assuming SQL server itself is running on the server, therefore when you specify C:\aud_df.xml SQL will look on the servers c:\ drive for the file not your local PC's c:\drive.
So you need to specify the full path including your computername in order for SQL to see the file. You will probably also need to setup a share on your local PC. Alternatively copy the xml files to the server and it should work.
|||Many thanks|||This sounds like a perfect job for SSIS!!
Cobr94 wrote: