How to use content of file for xml input?

Hi Everyone,
actually i'm starting to work with OpenXML on SQL Server 2k. At the
examples i found everywhere a variable was defined and filled
hardcode. Something like this:
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc = 'This is the content of my file'
EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
But i don't have any hardcoded text. I have a lot of xml files, which
liked to be prepared. They size vary from 1 MB to 100 MB and i don't
know how to pass them into the stored procedure.
So can anyone tell me, how a external xml file can be prepared to use
it with sp_xml_preparedocument? What functions are needed to read the
content of a given file and access it for a stored procedure?
Best regards,
Oliver
You write a stored procedure with an ntext typed parameter and you read the
XML on the client and pass it to the stored procedure as a parameter. You
will have to change encoding to UTF-16 on the midtier before you pass it in
though.
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
> Hi Everyone,
> actually i'm starting to work with OpenXML on SQL Server 2k. At the
> examples i found everywhere a variable was defined and filled
> hardcode. Something like this:
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc = 'This is the content of my file'
> EXEC sp_xml_prepraredocument @.idoc OUTPUT, @.doc
> But i don't have any hardcoded text. I have a lot of xml files, which
> liked to be prepared. They size vary from 1 MB to 100 MB and i don't
> know how to pass them into the stored procedure.
> So can anyone tell me, how a external xml file can be prepared to use
> it with sp_xml_preparedocument? What functions are needed to read the
> content of a given file and access it for a stored procedure?
> Best regards,
> Oliver
>
|||Hi Michael,
ok, but doesn't there any method exists to do it directly on the SQL
server? I don't need a real client. I just have to read a XML file
into a table. Couldn't it be done directly on the server?
Best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You write a stored procedure with an ntext typed parameter and you read the
> XML on the client and pass it to the stored procedure as a parameter. You
> will have to change encoding to UTF-16 on the midtier before you pass it in
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegro ups.com...
|||You could write your own extended stored procedure or use the sp_OA stored
procs. Neither I recommend. SQL Server 2005 can do it easily with
OpenRowset(BULK).
Best regards
Michael
"Oliver" <oliver.muenchow@.gmx.de> wrote in message
news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...
> Hi Michael,
> ok, but doesn't there any method exists to do it directly on the SQL
> server? I don't need a real client. I just have to read a XML file
> into a table. Couldn't it be done directly on the server?
> Best regards,
> Oliver
> Michael Rys [MSFT] schrieb:
>
|||Hi Michael,
thanks for the answer but i found a solution.
I used the bulk load mechanism. If you know what you need you know what
you have to search for. ;-) So after i read something about bulk load
and a little search i found the following website:
http://msdn2.microsoft.com/en-us/library/aa225754.aspx
With these mechasim i'm able to read the file into our SQL Server.
So thanks for your interest in my problem and best regards,
Oliver
Michael Rys [MSFT] schrieb:
[vbcol=seagreen]
> You could write your own extended stored procedure or use the sp_OA stored
> procs. Neither I recommend. SQL Server 2005 can do it easily with
> OpenRowset(BULK).
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167808359.527451.19810@.a3g2000cwd.googlegrou ps.com...