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,
OliverYou 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.googlegroups.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:
> You write a stored procedure with an ntext typed parameter and you read th
e
> 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 i
n
> though.
> Best regards
> Michael
> "Oliver" <oliver.muenchow@.gmx.de> wrote in message
> news:1167740889.192836.302290@.48g2000cwx.googlegroups.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.googlegroups.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:
> 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.googlegroups.com...
Showing posts with label everywhere. Show all posts
Showing posts with label everywhere. Show all posts
Wednesday, March 7, 2012
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...
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...
Subscribe to:
Posts (Atom)