How to populate a sp input parameter with an XML file

[batteam]

I have an application that needs to use the SQL sp_xml_preparedocument and OPENXML sp's. However, my original XML file will be very large and I need to 'feed' the whole file to the sp's Input Parameter so the two referenced sp's can work. Is there SQL code to read an XML file to create the needed Input Parameter? Or do you know of a way to send it over from an Access 2007 project?

Thanks for any help you can provide.

 

[mrdenny]

If the file exists on the SQL Server's disk or on a network share that the SQL Server can access you can use the BULK INSERT statement to read in the file from within the stored procedure.

If not (like it's on the users desktop) then you'll need to read the file into a variable using VBA code in Access and pass the value to the stored procedure's input parameter.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[batteam]

mrdenny: thanks for the tip. The file will be available on the Server's disk or at least in a shared network folder so I will look into using the BULK INSERT stmt. That should do it, I hope. Thanks again.

 

[gmmastros]

If the file is on a shared network folder, you may run in to permission problems. By default, SQL doesn't have access to any disks outside of the physical machine.

To allow access, you are best off creating a new active directory account that has full access to the SQL Server computer and access to the shared network folder, and nothing else. Make sure you set the password not to expire.

Next, open the "services" control panel, open the SQL Server service, and change the log in properties to the new active directory account you created.

This will allow you to look at files and folders on other network shares.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[batteam]

New problem with the Bulk insert stmt. My position here in my company does not have permission to use the Bulk command. Can OpenRecordSet be used without the Bulk command? If so, can you provide sampe code snippet?

Thanks.

 

[gmmastros]

Is there a reason why the DBA's in your company don't give you permissions to that functionality?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[batteam]

gmmastros: its just a security measure. Under almost all circumstances, my development applications do not need the Bulk command, so its not provided. Intent, I'm sure, is to prevent it being inadvertently misued and cause space problems, or something.

 

[gmmastros]

Right. I get that. But if you are writing code that requires BULK permissions, then you should get it. Right? I mean... isn't that the logical approach? Have you asked your DBA's for appropriate permissions?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[batteam]

Right. That's logical, but it won't be provided if its not needed, and that's what I'm trying to find out. Don't know if the OPENROWSET command needs the BULK command to work. I can't find anything in the books or on the 'net' that shows it working without the BULK command. If it can't be used without it, I will know what has to be done next. But right now, I guess that's my question. Thanks.

 

[mrdenny]

Using OPENROWSET is a hack in this case and will take much more resources than doing a bulk insert.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)
MCM (SQL 2008)
MVP

My Site

 

[batteam]

mrdenny: thanks for the input. I was sorta' expecting that this would be the case. Maybe it will be no big deal to bet Bulk command permissions. We shall see. Anyway, thanks again for you response.