Import of XML data.

Dear all,
I have been asked to look into the following.
Data is delivered in XML format.
(Assume wel formed XML and the stylesheet is present).
This data has to be imported in the database.
What are the possibilities in 2005?
What are the possibilities in 2000?
Especially are there enough possibilities to import the XML data 2000. Or do
we need a special workaround voor 2000 ?
My experience with XML is very limited. Once the data is present in the
database I'll will be able to transform the data in such a way that it fits
in the target tables.
Thanks for your time and attention,
Ben Brugman"ben brugman" <ben@.niethier.nl> wrote in message
news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
> Dear all,
> I have been asked to look into the following.
> Data is delivered in XML format.
> (Assume wel formed XML and the stylesheet is present).
> This data has to be imported in the database.
> What are the possibilities in 2005?
> What are the possibilities in 2000?
> Especially are there enough possibilities to import the XML data 2000. Or
> do we need a special workaround voor 2000 ?
> My experience with XML is very limited. Once the data is present in the
> database I'll will be able to transform the data in such a way that it
> fits in the target tables.
> Thanks for your time and attention,
> Ben Brugman
>
2005 has more XML related features, but you can import the data into 2000 as
well.
Take a look at the OPENXML command in the Books Online. You can use those
queries to pull the data from the XML document into whatever format you
wish.
Rick Sawtell|||Thank you, I hadn't thought about this possibility.
Does this say that I can not import directly from XML into 2000?
Because then I do not have to look further into that ally.
Thanks for your time and suggestion,
Ben Brugman
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> schreef in bericht
news:Op%232VF9oIHA.4912@.TK2MSFTNGP03.phx.gbl...
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:u00%23337oIHA.1952@.TK2MSFTNGP05.phx.gbl...
>> Dear all,
>> I have been asked to look into the following.
>> Data is delivered in XML format.
>> (Assume wel formed XML and the stylesheet is present).
>> This data has to be imported in the database.
>> What are the possibilities in 2005?
>> What are the possibilities in 2000?
>> Especially are there enough possibilities to import the XML data 2000. Or
>> do we need a special workaround voor 2000 ?
>> My experience with XML is very limited. Once the data is present in the
>> database I'll will be able to transform the data in such a way that it
>> fits in the target tables.
>> Thanks for your time and attention,
>> Ben Brugman
> 2005 has more XML related features, but you can import the data into 2000
> as well.
> Take a look at the OPENXML command in the Books Online. You can use those
> queries to pull the data from the XML document into whatever format you
> wish.
>
> Rick Sawtell
>|||"ben brugman" <ben@.niethier.nl> wrote in message
news:7c959$480ce938$53557893$11072@.cache90.multikabel.net...
> Thank you, I hadn't thought about this possibility.
> Does this say that I can not import directly from XML into 2000?
> Because then I do not have to look further into that ally.
> Thanks for your time and suggestion,
> Ben Brugman
>
That depends on your needs. You could put an XML column into a text/ntext
or sufficiently large varchar/nvarchar field in SQL Server 2000. But then
you would be treating the XML as a single column in a table. This can also
be done in 2005, but as an XML data type rather than string datatypes listed
above. 2005 also has other advantages like binding an XSD to the XML data
type.
In order to map the columns in a database table(s) to specific nodes in the
xml object, you would need to use the OPENXML with some XPATH queries.
It is relatively straightforward.
As far as outputting XML, there are several avenues for you to pursue. This
includes the SELECT ... FOR XML scenario as well as some others.
HTH
Rick Sawtell

IMPORT Multiple CSV Files to SQLSERVER Table

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from the network.

USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

--Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @.filepath varchar(500),
@.pattern varchar(100), @.TableName varchar(128)
as
set quoted_identifier off
declare @.query varchar(1000)
declare @.max1 int
declare @.count1 int
Declare @.filename varchar(100)
set @.count1 =0
create table #x (name varchar(200))
set @.query ='master.dbo.xp_cmdshell "dir '+@.filepath+@.pattern +' /b"'
insert #x exec (@.query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @.max1 = (select max(ID) from #y)
--print @.max1
--print @.count1
While @.count1 <= @.max1
begin
set @.count1=@.count1+1
set @.filename = (select name from #y where [id] = @.count1)
set @.query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
--print @.query
exec (@.query)
insert into logtable (query) select @.query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\\kb-02\C$\MyImport\', '*.csv', 'Account'
I am getting the following error:

Could not bulk insert because file '\\kb-02\C$\MyImport\Access is denied.' could not be opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006BMy guess would be that the NT Login being used by your SQL Server service doesn't have access to \\kb-02\c$ (which is a good thing). Try creating an explicit share and giving permission to the appropriate NT Login.

-PatP|||After SP3 the security context of the user executing XP_CMDSHELL is validated before it's executed in the context of SQL Server service account. Also, if the service is running under Local System, then NO NETWORK ACCESS IS ALLOWED, period. The service needs to run under a Domain User account, and the user that executes the XP_CMDSHELL needs to have sysadmin permission to successfully complete the operation. There is a way to avoid this by creating a scheduled task and then invoking it with sp_start_job. This also requires SQLAgent service to run under Domain Users account with WRITE privileges to the share, but does not require the invoking user to have anything special, - just EXECUTE permission to sp_start_job which is given to PUBLIC by default.

Import index and constain

Dear
I export the database table,SP,view,use..etc to script
file. After i run the script to another server to rebuild
the database. Only the table(index and constain) can't
import to new database but i have choice the option(index
and constain) when i export the database to script. What
is ths problem?
Many Thanks
JohnJohn
How did you export your objects?
Try using DTS , there is an optino "Transfer Objects" so you will find that
you can export indexes and keys.
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:066301c3c476$0eb20a80$a501280a@.phx.gbl...
> Dear
> I export the database table,SP,view,use..etc to script
> file. After i run the script to another server to rebuild
> the database. Only the table(index and constain) can't
> import to new database but i have choice the option(index
> and constain) when i export the database to script. What
> is ths problem?
> Many Thanks
> John

Import Data Status

Dear all,
I need to copy data from one DB to another DB using the Import Data
function. When I started the transfer, it ran for a certain period then
stopped at around 50% of progress for few hours. The status shown in the
dialog is 608, another case is showing 703. What is the meaning of the
statuses? How can I get reference information about the statuses?
Thanks for any help!
Tedmond
Tedmond
I have no idea about these statuses . Consider using T-SQL to copy the data
between the databases
INSERT INTO db1.dbo.Table1 (col1,col2....) SELECT col1,col2.... FROM
db2.dbo.Table2
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:234701C5-E121-4499-B291-3432C8917DA6@.microsoft.com...
> Dear all,
> I need to copy data from one DB to another DB using the Import Data
> function. When I started the transfer, it ran for a certain period then
> stopped at around 50% of progress for few hours. The status shown in the
> dialog is 608, another case is showing 703. What is the meaning of the
> statuses? How can I get reference information about the statuses?
> Thanks for any help!
> Tedmond
>

Import Data Status

Dear all,
I need to copy data from one DB to another DB using the Import Data
function. When I started the transfer, it ran for a certain period then
stopped at around 50% of progress for few hours. The status shown in the
dialog is 608, another case is showing 703. What is the meaning of the
statuses? How can I get reference information about the statuses?
Thanks for any help!
TedmondTedmond
I have no idea about these statuses . Consider using T-SQL to copy the data
between the databases
INSERT INTO db1.dbo.Table1 (col1,col2....) SELECT col1,col2.... FROM
db2.dbo.Table2
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:234701C5-E121-4499-B291-3432C8917DA6@.microsoft.com...
> Dear all,
> I need to copy data from one DB to another DB using the Import Data
> function. When I started the transfer, it ran for a certain period then
> stopped at around 50% of progress for few hours. The status shown in the
> dialog is 608, another case is showing 703. What is the meaning of the
> statuses? How can I get reference information about the statuses?
> Thanks for any help!
> Tedmond
>

Import Data Status

Dear all,
I need to copy data from one DB to another DB using the Import Data
function. When I started the transfer, it ran for a certain period then
stopped at around 50% of progress for few hours. The status shown in the
dialog is 608, another case is showing 703. What is the meaning of the
statuses? How can I get reference information about the statuses?
Thanks for any help!
TedmondTedmond
I have no idea about these statuses . Consider using T-SQL to copy the data
between the databases
INSERT INTO db1.dbo.Table1 (col1,col2....) SELECT col1,col2.... FROM
db2.dbo.Table2
"Tedmond" <Tedmond@.discussions.microsoft.com> wrote in message
news:234701C5-E121-4499-B291-3432C8917DA6@.microsoft.com...
> Dear all,
> I need to copy data from one DB to another DB using the Import Data
> function. When I started the transfer, it ran for a certain period then
> stopped at around 50% of progress for few hours. The status shown in the
> dialog is 608, another case is showing 703. What is the meaning of the
> statuses? How can I get reference information about the statuses?
> Thanks for any help!
> Tedmond
>

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
AnwarHello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/

import an xml-file to sql2005

Dear all,
I'm trying to import an xml-file by passing the path i.e.
"D:\sql\project\bill_count_1.xml" to an db-procedure as in-param.
As a result whole the file-content should be saved in one column.
using SQL-2005.
The table looks like
create table test
(
id identity
, doc xml
)
You can solve the problem with openxml, openrowset, bulk insert and bcp
if you use the path as a string directly in the code but I could'nt
manage the import by sending the file-path as in-param to a db-proc
Thanks,
Every kind of help appr.
BR
Anwar
Hello anwar,
There's isn't any magic proc that does this for you. it can use dynamic SQL
inside of your own proc and openrowset to make this happen.
Cheers,
Kent Tegels
http://staff.develop.com/ktegels/