Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
Dee
Hi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:

> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee
|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:

Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist an
d
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connectio
n
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:

> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a differenc
e
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You don't give the version of SQL Server that you are using! You can write
a
> stored procedure that will create the database/table if they do not exist
and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connect
ion
> properties.
> You could use OPENXML to load the file and compare the two entries (assumi
ng
> the same structure) and FOR XML to produce your output which would not nee
d
> DTS/SSIS.
> John
> "Dee" wrote:
>|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodin...-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
[vbcol=seagreen]
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
>|||But I also have SQL 2005 Standard installed. Can I do an Import/Export fro
m
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodin...-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) i
s
> probably the way to go.
> John
> "Dee" wrote:
>|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
[vbcol=seagreen]
> But I also have SQL 2005 Standard installed. Can I do an Import/Export f
rom
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
>

Automation

How can I import an xml file to SQL at the same time every night? I will
need to create a new database first via the import after that I will be
appending to the database. Then I need to xport the data into a difference
xml file.
Do I have to have the orginal xml file on my server or can I point to the
location of the xml file?
Thank you
DeeHi
You don't give the version of SQL Server that you are using! You can write a
stored procedure that will create the database/table if they do not exist and
then pass the database name to a DTS/SSIS package that will load the file.
Using this global variable for the package you can then change the connection
properties.
You could use OPENXML to load the file and compare the two entries (assuming
the same structure) and FOR XML to produce your output which would not need
DTS/SSIS.
John
"Dee" wrote:
> How can I import an xml file to SQL at the same time every night? I will
> need to create a new database first via the import after that I will be
> appending to the database. Then I need to xport the data into a difference
> xml file.
> Do I have to have the orginal xml file on my server or can I point to the
> location of the xml file?
> Thank you
> Dee|||John,
I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
and the standard for Windows XP installed.
Will this work for both.
Thanks
Dee
"John Bell" wrote:
> Hi
> You don't give the version of SQL Server that you are using! You can write a
> stored procedure that will create the database/table if they do not exist and
> then pass the database name to a DTS/SSIS package that will load the file.
> Using this global variable for the package you can then change the connection
> properties.
> You could use OPENXML to load the file and compare the two entries (assuming
> the same structure) and FOR XML to produce your output which would not need
> DTS/SSIS.
> John
> "Dee" wrote:
> > How can I import an xml file to SQL at the same time every night? I will
> > need to create a new database first via the import after that I will be
> > appending to the database. Then I need to xport the data into a difference
> > xml file.
> >
> > Do I have to have the orginal xml file on my server or can I point to the
> > location of the xml file?
> >
> > Thank you
> > Dee|||Hi
Import/Export and Integration services is not on the feature list for SQL
Express see
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
probably the way to go.
John
"Dee" wrote:
> John,
> I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> and the standard for Windows XP installed.
> Will this work for both.
> Thanks
> Dee
> "John Bell" wrote:
> > Hi
> >
> > You don't give the version of SQL Server that you are using! You can write a
> > stored procedure that will create the database/table if they do not exist and
> > then pass the database name to a DTS/SSIS package that will load the file.
> > Using this global variable for the package you can then change the connection
> > properties.
> >
> > You could use OPENXML to load the file and compare the two entries (assuming
> > the same structure) and FOR XML to produce your output which would not need
> > DTS/SSIS.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > How can I import an xml file to SQL at the same time every night? I will
> > > need to create a new database first via the import after that I will be
> > > appending to the database. Then I need to xport the data into a difference
> > > xml file.
> > >
> > > Do I have to have the orginal xml file on my server or can I point to the
> > > location of the xml file?
> > >
> > > Thank you
> > > Dee|||But I also have SQL 2005 Standard installed. Can I do an Import/Export from
there. I also have SQL 2005 Enterprise installed at work. How do I do it
from there?
Thanks Dee
"John Bell" wrote:
> Hi
> Import/Export and Integration services is not on the feature list for SQL
> Express see
> http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> probably the way to go.
> John
> "Dee" wrote:
> > John,
> >
> > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > and the standard for Windows XP installed.
> >
> > Will this work for both.
> >
> > Thanks
> > Dee
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You don't give the version of SQL Server that you are using! You can write a
> > > stored procedure that will create the database/table if they do not exist and
> > > then pass the database name to a DTS/SSIS package that will load the file.
> > > Using this global variable for the package you can then change the connection
> > > properties.
> > >
> > > You could use OPENXML to load the file and compare the two entries (assuming
> > > the same structure) and FOR XML to produce your output which would not need
> > > DTS/SSIS.
> > >
> > > John
> > >
> > > "Dee" wrote:
> > >
> > > > How can I import an xml file to SQL at the same time every night? I will
> > > > need to create a new database first via the import after that I will be
> > > > appending to the database. Then I need to xport the data into a difference
> > > > xml file.
> > > >
> > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > location of the xml file?
> > > >
> > > > Thank you
> > > > Dee|||Hi Dee
You would be able to run a package on the Std edition that connected to the
Express edition and populated it, but what you are trying to achieve should
be codable in T-SQL without the need for a package, therefore it can be run
from a command prompt and SQLCMD on the machine that is running SQL Express.
This may help http://www.sqlis.com/31.aspx
John
"Dee" wrote:
> But I also have SQL 2005 Standard installed. Can I do an Import/Export from
> there. I also have SQL 2005 Enterprise installed at work. How do I do it
> from there?
> Thanks Dee
> "John Bell" wrote:
> > Hi
> >
> > Import/Export and Integration services is not on the feature list for SQL
> > Express see
> > http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.
> > Therefore using OPENXML and FOR XML (use BCP or SQLCMD to create a file) is
> > probably the way to go.
> >
> > John
> >
> > "Dee" wrote:
> >
> > > John,
> > >
> > > I am using SQl 2005 on Windows XP. I have the SQl 2005 express installed
> > > and the standard for Windows XP installed.
> > >
> > > Will this work for both.
> > >
> > > Thanks
> > > Dee
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You don't give the version of SQL Server that you are using! You can write a
> > > > stored procedure that will create the database/table if they do not exist and
> > > > then pass the database name to a DTS/SSIS package that will load the file.
> > > > Using this global variable for the package you can then change the connection
> > > > properties.
> > > >
> > > > You could use OPENXML to load the file and compare the two entries (assuming
> > > > the same structure) and FOR XML to produce your output which would not need
> > > > DTS/SSIS.
> > > >
> > > > John
> > > >
> > > > "Dee" wrote:
> > > >
> > > > > How can I import an xml file to SQL at the same time every night? I will
> > > > > need to create a new database first via the import after that I will be
> > > > > appending to the database. Then I need to xport the data into a difference
> > > > > xml file.
> > > > >
> > > > > Do I have to have the orginal xml file on my server or can I point to the
> > > > > location of the xml file?
> > > > >
> > > > > Thank you
> > > > > Dee

Automating XML from Web to SQL Server

I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant

Automatically export foxpro data to an xml file

I'm not sure if this is the correct place to post this, so I apologize
if it isn't.
I have a foxpro table that needs to be accessed by the web, but I don't
really want to access it with odbc unless I have to. I'd rather just
export the data once a night and read it from that exported file.
My questions is this: Is there a program out there that will export a
foxpro table to an xml file like once a night (or at any specified
interval)?
Any other solutions somebody can think of would be helpful. Thanks!Luke.Visinoni@.gmail.com wrote:
> I'm not sure if this is the correct place to post this, so I apologize
> if it isn't.
> I have a foxpro table that needs to be accessed by the web, but I don't
> really want to access it with odbc unless I have to. I'd rather just
> export the data once a night and read it from that exported file.
> My questions is this: Is there a program out there that will export a
> foxpro table to an xml file like once a night (or at any specified
> interval)?
> Any other solutions somebody can think of would be helpful. Thanks!
>
Since this is a SQL Server newsgroup, I'll suggest a SQL Server
solution... :-)
You could setup a linked server between SQL and your Foxpro database,
and then use a scheduled SQL job to export the data from Foxpro to an
XML file. Or just let your web site connect to SQL like the rest of the
world does.|||How would I set up a linked server?
Tracy McKibben wrote:
> Since this is a SQL Server newsgroup, I'll suggest a SQL Server
> solution... :-)
> You could setup a linked server between SQL and your Foxpro database,
> and then use a scheduled SQL job to export the data from Foxpro to an
> XML file. Or just let your web site connect to SQL like the rest of the
> world does.|||Luke.Visinoni@.gmail.com wrote:
> I'm not sure if this is the correct place to post this, so I apologize
> if it isn't.
> I have a foxpro table that needs to be accessed by the web, but I don't
> really want to access it with odbc unless I have to. I'd rather just
> export the data once a night and read it from that exported file.
> My questions is this: Is there a program out there that will export a
> foxpro table to an xml file like once a night (or at any specified
> interval)?
> Any other solutions somebody can think of would be helpful. Thanks!
>
Since this is a SQL Server newsgroup, I'll suggest a SQL Server
solution... :-)
You could setup a linked server between SQL and your Foxpro database,
and then use a scheduled SQL job to export the data from Foxpro to an
XML file. Or just let your web site connect to SQL like the rest of the
world does.|||How would I set up a linked server?
Tracy McKibben wrote:
> Since this is a SQL Server newsgroup, I'll suggest a SQL Server
> solution... :-)
> You could setup a linked server between SQL and your Foxpro database,
> and then use a scheduled SQL job to export the data from Foxpro to an
> XML file. Or just let your web site connect to SQL like the rest of the
> world does.|||Luke.Visinoni@.gmail.com wrote:
> How would I set up a linked server?
> Tracy McKibben wrote:
>
Try to look up sp_addlinkedserver in Books On Line - that will get you
started.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Luke.Visinoni@.gmail.com wrote:
> How would I set up a linked server?
> Tracy McKibben wrote:
>
Try to look up sp_addlinkedserver in Books On Line - that will get you
started.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi Luke,
First, make sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
To set up a linked server in SQL Server Management Studio's Object Explorer:
ServerName > Server Objects > Linked Servers > Right Click - New Linked
Server. In the dialog here's what I have:
Linked Server: LinkedServerNameHere
Provider: Microsoft OLE DB Provider for Visual FoxPro
Product Name: Visual FoxPro 9
Data Source: "C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc"
Provider String: VFPOLEDB.1
For the data source, if there is a DBC file present point directly to the
DBC file. If there is no DBC present just point to the directory where the
DBFs are located.
To access the data use code like:
Select Customers.* From LinkedServerNameHere...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<Luke.Visinoni@.gmail.com> wrote in message
news:1150833731.102678.204090@.c74g2000cwc.googlegroups.com...
> How would I set up a linked server?
> Tracy McKibben wrote:
>|||Hi Luke,
First, make sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
To set up a linked server in SQL Server Management Studio's Object Explorer:
ServerName > Server Objects > Linked Servers > Right Click - New Linked
Server. In the dialog here's what I have:
Linked Server: LinkedServerNameHere
Provider: Microsoft OLE DB Provider for Visual FoxPro
Product Name: Visual FoxPro 9
Data Source: "C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc"
Provider String: VFPOLEDB.1
For the data source, if there is a DBC file present point directly to the
DBC file. If there is no DBC present just point to the directory where the
DBFs are located.
To access the data use code like:
Select Customers.* From LinkedServerNameHere...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<Luke.Visinoni@.gmail.com> wrote in message
news:1150833731.102678.204090@.c74g2000cwc.googlegroups.com...
> How would I set up a linked server?
> Tracy McKibben wrote:
>

Automatically export foxpro data to an xml file

I'm not sure if this is the correct place to post this, so I apologize
if it isn't.
I have a foxpro table that needs to be accessed by the web, but I don't
really want to access it with odbc unless I have to. I'd rather just
export the data once a night and read it from that exported file.
My questions is this: Is there a program out there that will export a
foxpro table to an xml file like once a night (or at any specified
interval)?
Any other solutions somebody can think of would be helpful. Thanks!Luke.Visinoni@.gmail.com wrote:
> I'm not sure if this is the correct place to post this, so I apologize
> if it isn't.
> I have a foxpro table that needs to be accessed by the web, but I don't
> really want to access it with odbc unless I have to. I'd rather just
> export the data once a night and read it from that exported file.
> My questions is this: Is there a program out there that will export a
> foxpro table to an xml file like once a night (or at any specified
> interval)?
> Any other solutions somebody can think of would be helpful. Thanks!
>
Since this is a SQL Server newsgroup, I'll suggest a SQL Server
solution... :-)
You could setup a linked server between SQL and your Foxpro database,
and then use a scheduled SQL job to export the data from Foxpro to an
XML file. Or just let your web site connect to SQL like the rest of the
world does.|||How would I set up a linked server?
Tracy McKibben wrote:
> Since this is a SQL Server newsgroup, I'll suggest a SQL Server
> solution... :-)
> You could setup a linked server between SQL and your Foxpro database,
> and then use a scheduled SQL job to export the data from Foxpro to an
> XML file. Or just let your web site connect to SQL like the rest of the
> world does.|||Luke.Visinoni@.gmail.com wrote:
> How would I set up a linked server?
> Tracy McKibben wrote:
>> Since this is a SQL Server newsgroup, I'll suggest a SQL Server
>> solution... :-)
>> You could setup a linked server between SQL and your Foxpro database,
>> and then use a scheduled SQL job to export the data from Foxpro to an
>> XML file. Or just let your web site connect to SQL like the rest of the
>> world does.
>
Try to look up sp_addlinkedserver in Books On Line - that will get you
started.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Hi Luke,
First, make sure you have the latest FoxPro and Visual FoxPro OLE DB data
provider, downloadable from msdn.microsoft.com/vfoxpro/downloads/updates.
To set up a linked server in SQL Server Management Studio's Object Explorer:
ServerName > Server Objects > Linked Servers > Right Click - New Linked
Server. In the dialog here's what I have:
Linked Server: LinkedServerNameHere
Provider: Microsoft OLE DB Provider for Visual FoxPro
Product Name: Visual FoxPro 9
Data Source: "C:\Program Files\Microsoft Visual FoxPro
9\Samples\Northwind\Northwind.dbc"
Provider String: VFPOLEDB.1
For the data source, if there is a DBC file present point directly to the
DBC file. If there is no DBC present just point to the directory where the
DBFs are located.
To access the data use code like:
Select Customers.* From LinkedServerNameHere...Customers
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
<Luke.Visinoni@.gmail.com> wrote in message
news:1150833731.102678.204090@.c74g2000cwc.googlegroups.com...
> How would I set up a linked server?
> Tracy McKibben wrote:
>> Since this is a SQL Server newsgroup, I'll suggest a SQL Server
>> solution... :-)
>> You could setup a linked server between SQL and your Foxpro database,
>> and then use a scheduled SQL job to export the data from Foxpro to an
>> XML file. Or just let your web site connect to SQL like the rest of the
>> world does.
>